Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

synthetic key when joining resident load tables with group by

I'm stuck with this one problem for quite a few days.

I've got my fact table with the fields :   itemID, Stage, Creation date, orderID

For each Stage I needed to create  resident loads , grouped by  orderID  to get the  itemID with the latest creationDate .

I now want to present a table that each row will have altogether information for every orderID, for example, fields would look like:

orderID (key) , stage1, latestcreation date stage 1, itemID stage 1, stage2, latest creation date stage 2, itemID stage 2  and so on.

But my problem is that even if i want to keep orderID as it is and join the temporary tables with the resident loads on this field, i still am not allowed by the script to  change the name of itemID to prevent the synthetic key  to, say,  "item id stage 1" , because it's coming from the fact table.

 

What is your advice? If you need any more information of further clarificiation, please ask me because i really need to resolve this 😞

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

The source table remains in the Data Model at the end, I called it TempOrders.
I changed the code a little bit now to rename TempOrders to Orders, removed the LatestCreationDate field from the Orders table and also the ItemID from the aggregation function that returns the latest creation date - this time is only by OrderID and Stage. I see some exceptions that may occur but I'll need to see your real data in order to treat those exceptions - if those happens at all.

Please see if it works now:

Orders:
LOAD * INLINE [
OrderID, ItemID, Stage, CreationDate
1,1,Stage 1, 01/01/2020
1,1,Stage 1, 03/01/2020
1,2,Stage 1, 04/01/2020
2,1,Stage 1, 01/01/2020
2,1,Stage 2, 01/01/2020
2,1,Stage 3, 01/01/2020
3,1,Stage 1, 01/01/2020
3,1,Stage 1, 02/01/2020
];

LEFT JOIN (Orders)
LatestCreationDates:
LOAD
	OrderID,
    Stage,
    Date(Max(CreationDate)) AS LatestCreationDate
RESIDENT
	Orders
GROUP BY
	OrderID,
    Stage	
;

For Each vStage In FieldValueList('Stage')

	[$(vStage) Details]:
	LOAD
		OrderID,
        ItemID AS [ItemID $(vStage)],
        Stage AS [Stage $(vStage)],
        LatestCreationDate AS [Latest Creation Date $(vStage)]
    RESIDENT
		Orders
	WHERE
    	Stage = '$(vStage)'
        AND CreationDate = LatestCreationDate
	;
    
Next vStage

DROP FIELD LatestCreationDate FROM [Orders];

 

View solution in original post

9 Replies
Highlighted
Partner
Partner

Can we have a Data Sample of your table?

Highlighted
Partner
Partner

Since I don't have your records, I'm doing some assumptions in terms of how the data is organized.
Anyway, I hope this code helps you.

TempOrders:
LOAD * INLINE [
OrderID, ItemID, Stage, CreationDate
1,1,Stage 1, 01/01/2020
1,1,Stage 1, 03/01/2020
1,2,Stage 1, 04/01/2020
2,1,Stage 1, 01/01/2020
2,1,Stage 2, 01/01/2020
2,1,Stage 3, 01/01/2020
3,1,Stage 1, 01/01/2020
3,1,Stage 1, 02/01/2020
];

LEFT JOIN (TempOrders)
LatestCreationDates:
LOAD
	OrderID,
    ItemID,
    Stage,
    Date(Max(CreationDate)) AS LatestCreationDate
RESIDENT
	TempOrders
GROUP BY
	OrderID,
    ItemID,
    Stage	
;

For Each vStage In FieldValueList('Stage')

	[$(vStage) Details]:
	LOAD
		OrderID,
        ItemID AS [ItemID $(vStage)],
        Stage AS [Stage $(vStage)],
        LatestCreationDate AS [Latest Creation Date $(vStage)]
    RESIDENT
		TempOrders
	WHERE
    	Stage = '$(vStage)'
        AND CreationDate = LatestCreationDate
	;
    
Next vStage

 

This code creates the Data Model below:

mark_costa_0-1598297972504.png

 

Then it is just a matter of doing your charts:

mark_costa_1-1598298333520.png

 

Regards,

Mark Costa

Highlighted
Creator II
Creator II

Hi @mark_costa , thank you, i will try it out and let you now how it went 🙂 

Highlighted
Creator II
Creator II

@mark_costa when I have multiple itemIDs for an orderID this code doesn't return just the latest CreationDate and the corresponding itemID for this orderID.  

I tried with firstSortedValue but doesnt work either.  Any ideas?

Highlighted
Creator II
Creator II

also i do not want to eliminate my source table! 

Highlighted
Partner
Partner

The source table remains in the Data Model at the end, I called it TempOrders.
I changed the code a little bit now to rename TempOrders to Orders, removed the LatestCreationDate field from the Orders table and also the ItemID from the aggregation function that returns the latest creation date - this time is only by OrderID and Stage. I see some exceptions that may occur but I'll need to see your real data in order to treat those exceptions - if those happens at all.

Please see if it works now:

Orders:
LOAD * INLINE [
OrderID, ItemID, Stage, CreationDate
1,1,Stage 1, 01/01/2020
1,1,Stage 1, 03/01/2020
1,2,Stage 1, 04/01/2020
2,1,Stage 1, 01/01/2020
2,1,Stage 2, 01/01/2020
2,1,Stage 3, 01/01/2020
3,1,Stage 1, 01/01/2020
3,1,Stage 1, 02/01/2020
];

LEFT JOIN (Orders)
LatestCreationDates:
LOAD
	OrderID,
    Stage,
    Date(Max(CreationDate)) AS LatestCreationDate
RESIDENT
	Orders
GROUP BY
	OrderID,
    Stage	
;

For Each vStage In FieldValueList('Stage')

	[$(vStage) Details]:
	LOAD
		OrderID,
        ItemID AS [ItemID $(vStage)],
        Stage AS [Stage $(vStage)],
        LatestCreationDate AS [Latest Creation Date $(vStage)]
    RESIDENT
		Orders
	WHERE
    	Stage = '$(vStage)'
        AND CreationDate = LatestCreationDate
	;
    
Next vStage

DROP FIELD LatestCreationDate FROM [Orders];

 

View solution in original post

Highlighted
Creator II
Creator II

Thank you @mark_costa , i'm truly thankful! But, I don't understand why we had to remove the field latestcreationdate  from the source table in the end, in order to get just one date when we had already grouped by orderID! Can you help me understand?

 

 

Highlighted
Partner
Partner

You don't need to. I just removed to keep the original table as it is. You can leave the field there, no problem.

Highlighted
Creator II
Creator II

Okay, and one last question, why wouldn't it work without this  part of the where  clause?

"AND CreationDate= LatestCreationDate"

I mean isn't already declared how it works in the table above?