Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SCruise
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
jensmunnichs
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
jensmunnichs
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!

SCruise
Contributor
Contributor
Author

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!

 

andrew_smith200
Contributor III
Contributor III

you would need to group by timestamp not Overall Status