cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahall
Level: Powered On

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
Microsoft v-siky-msft
Microsoft

Re: join two sql tables

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
Highlighted
shailendra74
Level 10

Re: join two sql tables

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.

 

 

 

 

Microsoft v-siky-msft
Microsoft

Re: join two sql tables

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

ahall
Level: Powered On

Re: join two sql tables

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

ahall
Level: Powered On

Re: join two sql tables

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,114)