Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Left Join within a single Table?

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?

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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!

View solution in original post

3 Replies
Highlighted
Creator III
Creator III

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!

View solution in original post

Highlighted
Contributor
Contributor

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!

 

Highlighted
Contributor III
Contributor III

you would need to group by timestamp not Overall Status