How combine 2 DataSets to for a Card with different colums

Reply
Highlighted
White Belt

How combine 2 DataSets to for a Card with different colums

Hello, I have a task here that I am stuck with. I am trying to build a card which looks like the attached.

 

and in order to do that, the open and closed orders amount are from a different dataset and the forecast amount is on another dataset then I have to get the total forecast amount to be shown in one bar and open and closed orders on the second bar

 

How do i combine the two dataset in order for me to create the card?

Note that the two dataset has different column names and columns such as fiscal year and month has different data type also.

 
like for example: Fiscal Year from DataSet1 is Text and DataSet2 is Whole Number

 

I tried SELECT*FROM DataSet1 JOIN DataSet2 but it fails.

 

I already know how to create the card itself, I am stuck with how to combine the data in order for me to get accurate numbers. Thanks!


Accepted Solutions
Yellow Belt

Re: How combine 2 DataSets to for a Card with different colums

Hey @drewfig,

 

One way to do is select all the columns that have common field or normalize in a way they are uniform across all the dataset and stack it.  To normalize the date you can use the concat function for those that has month and year as a separate column: 

DATE(CONCAT(year,month,'01')) AS Date  [just give 1 for that month so we can have in date format]

 

 

Then use the stack and group graph. Use Date on x-axis, amount on y axis and metric on series.

 

For ex:

 

SELECT Date ,Product, Amount, 'Open Orders' as metric from table1 

UNION ALL

SELECT Date ,Product, Amount, 'Closed Orders' as metric from table2 

UNION ALL

SELECT Date ,Product, Amount, 'Forecast' as metric from table3

 

Let me know if you need more clarification. 


All Replies
Blue Belt

Re: How combine 2 DataSets to for a Card with different colums

Hi,

Can you send your dataset schemas? And point out how you want to join them?

Regards

Ricardo Granada 


MajorDomo@Lusiaves



**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.
White Belt

Re: How combine 2 DataSets to for a Card with different colums

Please see attached, not sure if that's the one you are looking for.

 

I was planning to join Product_Number (forecast) and MODEL1 (OpenClosed), Fiscal_Year (forecast)  and Fiscal Year (OpenClosed) and Fiscal_Month (forecast) and Fiscal Month (OpenClosed) . 

 

Thanks!

Yellow Belt

Re: How combine 2 DataSets to for a Card with different colums

Hey @drewfig,

 

One way to do is select all the columns that have common field or normalize in a way they are uniform across all the dataset and stack it.  To normalize the date you can use the concat function for those that has month and year as a separate column: 

DATE(CONCAT(year,month,'01')) AS Date  [just give 1 for that month so we can have in date format]

 

 

Then use the stack and group graph. Use Date on x-axis, amount on y axis and metric on series.

 

For ex:

 

SELECT Date ,Product, Amount, 'Open Orders' as metric from table1 

UNION ALL

SELECT Date ,Product, Amount, 'Closed Orders' as metric from table2 

UNION ALL

SELECT Date ,Product, Amount, 'Forecast' as metric from table3

 

Let me know if you need more clarification. 

Announcements
Domopalooza 2018! Pre-conference training registration is now open! Click here!