topic Re: Left Join within a single Table? in App Development

Left Join within a single Table?

SCruise — Sat, 16 Nov 2024 18:56:41 GMT

Hi All,

I'm trying to figure out how I can adjust the below load script to flatten the data into a single row. I thought what I needed was Left Join, but that seems to not be making any difference as I'm not joining tables, the data I'm trying to flatten already is in a single table. I don't want to combine the text into a single field, so Concat is out.

Reading the information on CrossTable and Generic, those as well don't appear to be what I need. I tried specifying now(), thinking there was a change in the variable, but that doesn't seem to be the case.

I found a few previous articles related to merging information, but my end goad is not to concat data into a single cell, or use Generic/CrossTable to switch rows/columns.

Here's the load script I'm working with in QlikSense Enterprise. The Table Request ID is a sharepoint site, so there is no concern with needing Distinct, as their IDs are always Distinct.

Let vDateStamp = now(0); WeeklyTotals: Load ('$(vDateStamp)') as TimeStamp, if(OverallStatus='Overdue',count([Request ID])) as WT.Overdue, if(OverallStatus='Open',count([Request ID])) as WT.Open, if(OverallStatus='Closed',count([Request ID])) as WT.Closed Resident [Request Site] Group By OverallStatus; Drop Field OverallStatus;

The end result is:
WeeklyTotals

TimeStamp	WT.Overdue	WT.Open	WT.Closed
12.03.2020 21:05:45	38	-	-
12.03.2020 21:05:45	-	102	-
12.03.2020 21:05:45	-	-	573

The end result I'm trying to get to is this:

TimeStamp	WT.Overdue	WT.Open	WT.Closed
12.03.2020 21:05:45	38	102	573

I ask this as step 2 is to schedule this in script in QMC to run once a week and Insert Only to the existing table, resulting in a slow growing table that roughly looks like this:

TimeStamp	WT.Overdue	WT.Open	WT.Closed
12.03.2020 21:05:45	38	102	573
19.03.2020 21:05:40	20	89	605
26.03.2020 21:06:14	22	41	653

My Questions:

1) Why is it creating three timestamps and a value for each field? I was thinking since it's a single load, it would be a single row, but this is not the case.

2) How do I, using the field TimeStamp, merge the there rows into a single row?

Re: Left Join within a single Table?

jensmunnichs — Fri, 13 Mar 2020 16:32:32 GMT

1) I think because you are grouping by OverallStatus. You are basically saying, give me these 4 fields for every distinct value in OverallStatus. So you're making this table:

OverallStatus

TimeStamp
WT.Overdue
WT.Open
WT.Closed
Overdue
12.03.2020 21:05:45
38
-
-
Open
12.03.2020 21:05:45
-
102
-
Closed
12.03.2020 21:05:45
-
-
573

Except you can't see OverallStatus, as you did not load this as a field. But I think you can see now why every TimeStamp is tripled and you get nulls in the Overdue/Open/Closed columns.

2) To fix this, you will have to group by the TimeStamp field instead.

Hope this helps!

Re: Left Join within a single Table?

SCruise — Fri, 13 Mar 2020 18:52:29 GMT

Okay, that makes sense. I was indeed able to confirm that is exactly what is happening; There is a line for each status as you show in your table. Your post lead me down a rabbit hole, which I'll post my end result below encase someone comes across this in the future.

Grouping by TimeStamp makes sense. It seems like I'll need a interim table to make that work since TimeStamp is define in the above load script and thus I cannot: Resident Load [Request Site] Group By OverallStatus, TimeStamp;

The issue becomes trying to group by without an Aggregate Function. I only have a single timestamp, so Only(TimeStamp) sould work, but that returned an error when using the following:

This was a pain in the side until I came across this post, which lead me to adjust the second table to:

WeeklyTotalsInOneRow: Load Only(TimeStamp), Only([WT.Overdue]), Only([WT.Open]), Only([WT.Closed]) Resident WeeklyTotals group by TimeStamp;

Works like a charm, Thanks!

Re: Left Join within a single Table?

andrew_smith200 — Fri, 13 Mar 2020 20:19:09 GMT

you would need to group by timestamp not Overall Status

OverallStatus	TimeStamp	WT.Overdue	WT.Open	WT.Closed
Overdue	12.03.2020 21:05:45	38	-	-
Open	12.03.2020 21:05:45	-	102	-
Closed	12.03.2020 21:05:45	-	-	573