Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
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!
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:
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;
WeeklyTotalsInOneRow:
Load Only(TimeStamp),
[WT.Overdue],
[WT.Open],
[WT.Closed]
Resident WeeklyTotals group by TimeStamp;
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!