cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

join two sql tables

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

 

sub.JPG    unsub.JPG   join.JPG
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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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)))

Annotation 2019-08-27 110909.png

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

 

 

 

 

Community Support
Community Support

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)))

Annotation 2019-08-27 110909.png

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

View solution in original post

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

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.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (103,965)