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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,388)