New to Qlik Sense

[ioannagr]

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 😞

[mark_costa]

Can we have a Data Sample of your table?

[mark_costa]

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:

Then it is just a matter of doing your charts:

Regards,

Mark Costa

[ioannagr]

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

[ioannagr]

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

[ioannagr]

also i do not want to eliminate my source table! 

[mark_costa · Accepted solution]

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

[ioannagr]

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?

[mark_costa]

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

[ioannagr]

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?