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: 
ioannagr
Creator III
Creator III

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
marksouzacosta
Partner - Creator II
Partner - Creator II

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];

 

Get hooked on Qlik at qlikbait.net

View solution in original post

9 Replies
marksouzacosta
Partner - Creator II
Partner - Creator II

Can we have a Data Sample of your table?

Get hooked on Qlik at qlikbait.net
marksouzacosta
Partner - Creator II
Partner - Creator II

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

Get hooked on Qlik at qlikbait.net
ioannagr
Creator III
Creator III
Author

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

ioannagr
Creator III
Creator III
Author

@marksouzacosta 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?

ioannagr
Creator III
Creator III
Author

also i do not want to eliminate my source table! 

marksouzacosta
Partner - Creator II
Partner - Creator II

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];

 

Get hooked on Qlik at qlikbait.net
ioannagr
Creator III
Creator III
Author

Thank you @marksouzacosta , 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?

 

 

marksouzacosta
Partner - Creator II
Partner - Creator II

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

Get hooked on Qlik at qlikbait.net
ioannagr
Creator III
Creator III
Author

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?