How to link Two qurey(datasets) data

Question & Answer
, ,
1

i using two direct query to build Datasets in SPICE, and i want to link them into one quicksight analysis.

query1 is the main data, have PO, ASIN, etc informations
query2 is the support data also have PO, ASIN information, i set this as a datasets want to link to query1. i want to use ‘CONCATENATE’ PO&ASIN as unique value same as Excel format to link query2 data to query1?
Does anyone know how to do it? Thanks

2

Hi,

There are many ways to do this. Simple you can upload both the files in a dataset.
create a join between them like example given below
Large file on the lift side and short on right side if you are using the lift join.

Join both file POs in given below join configuration.

image
image1465×796 39 KB

if you are not familiar with joins, you need pre your data in excel before upload :slight_smile:

Let me know if not fixed, I will support you :slight_smile:

Regards
Naveed Ali

3

Hi ,
i using the query to get the data directly, i did not have the offline excel file to upload.

4

Hi,

You can create the view on the both the tables.

like select *(concatenate both the column ) from query1 q1 lift join query2 q2 on q1.po = q2.po

or join both the queries at SPICE dataset.

Regards
Naveed Ali

5

Hi,
i’m using the query share from the team, so i don’t want to edit the query to avoid data error.

so I seeking if there have any easy way to link the data by concatenate PO&ASIN :rofl:

anyway thanks for your comment.

图片
图片539×637 36 KB

6

Hi,

Just follow as I guide you first reply. :slight_smile: Create a new join here I think this will be resolved.

Regards,
Naveed Ali

7

Hi @Chloe5,

Do you need to concatenate PO and ASIN? You can create your join by using 2 join clauses, one for PO and another for ASIN.

8

Hello @Chloe5 , @Naveed , and @David_Wong !

@Chloe5 are you still working on this problem or were you able to find a solution? If @Naveed or @David_Wong 's suggestions helped solve this could you mark that comment as a solution to help the community?

It has been some time since we have heard from you but want to help find a solution. If we do not hear from you in the next 2 business days this topic will be archived.

9

Hi , @David_Wong
i have try using 2 join to connect the data, but find some data rows are duplicate.
eg. in the data it have duplicate PO, ASIN row with different qty, like PO1-ASIN1- QTY1, PO1-ASIN1-QTY2. when join the data it will make my main data result become double.

10

Hi @Chloe5,

You need to use level-aware calculations to handle the duplicates. Take a look at use case #2 in this article.

11

thanks sharing, will learning.