Answered

join two sql tables

(0) Share

Report

Posted on by ahall

I have two basic sql tables I need to join so I can display the data for each on the same line graph.

I can make seperate line graphs of each table seperately with date on the x and number on the Y. I simply want the date on the botom and number on the Y axis with a different line for each subscribed and unsubscribed

my two tables are subscription and unsubscribe. I'd like the result to be the simple joined

I think my solution lies in thes answer but I can't get it right.

https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi @ahall

You can try as follows:

ClearCollect(myCollection, ShowColumns(AddColumns('Subscription Table', "UnsubscribeNumber",
Value(LookUp('UnSubscribe Table',
Text(subdate, "[$-en-US]dd/mm/yyyy") = Text(unsubdate, "[$-en-US]dd/mm/yyyy"), unsubnumber))),
"subdate", "subnumber", "UnsubscribeNumber"))
By using Value function, if unsubscribednumber is not available for given date you will get 0.

Based on the output place a line graph, with Items property as myCollection and set "Number of Series" to 2 (to see 2 line graphs)

Thanks.

Was this reply helpful? Yes No
Verified answer

v-siky-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @ahall ,

The issue you have is that the ‘unsubscribedNumber’ column have no value in the joined table, right?
What is the type of ‘date’ column, DateTime?

I have made a test on my side, and find the issue may be due to the ‘date’ column, the date type column is unable to match successfully in LookUp.
Here is two workarounds.
1. Use text or value function to convert date to text/value, so that they can join successfully. But delegation should notice.
You can refer to the formula:
ClearCollect(myCollection,AddColumns('[dbo].[Subscription Table]',"unsubscribedNumber",LookUp('[dbo].[Unsub Table]',Text(date,DateTimeFormat.ShortDate)=Text('[dbo].[Subscription Table]'[@date],DateTimeFormat.ShortDate),unsubscribedNumber)))
2. Or you can add another index column (Text/Value type), and use it to match in LookUp instead of ‘data’ column, so that there is no need to worry about the delegation warning.

Best regards,
Sik

Was this reply helpful? Yes No
ahall 42 on at

Like (0)

Report
Copy link

Link copied!

thanks so much, basically the same as the other answer I was able to omit the ShowColumn.

Was this reply helpful? Yes No
ahall 42 on at

Like (0)

Report
Copy link

Link copied!

thanks, I was able to make it work. I might be best to add a third column with just a text representation of date as I don't need a real date object.

Was this reply helpful? Yes No