Lookup a value in a changelog

[BartVA]

I have a changelog table of statuses per SKU, and need to add the correct status to the SalesInvoice table.
See example below with sample tables and the desired result. How do I solve this problem?

If possible I'd like to use mapping instead of joining to be absolutely certain no SalesInvoice lines are duplicated because of duplicate statuses in the StatusChangeLog. (In case of duplicate statuses, maybe it would be good to have "Multiple" as result Status, so the error is visible and can be corrected in the data.)

tSalesInvoices:
Load * Inline
[
ID, SKU, SalesTimestamp
1, A, 2024-05-01 19:44
2, B, 2024-05-06 17:45
3, A, 2024-05-06 18:10
4, A, 2024-05-06 18:20
5, B, 2024-05-07 16:10
6, A, 2024-05-08 07:45
7, B, 2024-05-09 08:02];

tStatusChangeLog:
//On irregular moments the list with current statuses per SKU is polled and stored. If status = NULL, no record is made.
Load * Inline
[
LogTimestamp, SKU, Status
2024-05-03 08:00, A, S1
2024-05-03 14:17, A, S1
2024-05-04 12:12, A, S1
2024-05-06 18:15, A, S1
2024-05-06 18:15, B, S3
2024-05-07 20:15, B, S2];

/*
Desired result:
Add to each tSalesInvoices line the correct status.
Statuses after the last record must be interpreted as "No Status". In case of Multiple statuses for the same SKU in the same time period, "Multiple".

tSalesInvoices:
ID, SKU, SalesTimestamp, Status
1, A, 2024-05-01 19:44, No Status	//No status for A before 2024-05-03
2, B, 2024-05-06 17:45, No Status 	//No status for B before 2024-05-06
3, A, 2024-05-06 18:10, S1   		//A had status S1 from 2024-05-03 until 2024-05-06 18:15
4, A, 2024-05-06 18:20, No Status	//5 minutes past the last status for A
5, B, 2024-05-07 16:10, S3			//B had status S3 from 2024-05-06 18:15 until 2024-05-07 20:15, when it changed to S2
6, A, 2024-05-08 07:45, No Status	//No status for A after 2024-05-06
7, B, 2024-05-09 08:02, No Status	//No Status for B after 2024-05-07

[marcus_sommer · Accepted solution]

If I understand it right the matching goes not only against SKU else the timestamps must be considered. If so you couldn't match the data directly else it needs multiple preparing-steps.

Such steps may be to load the data resident to be able to order the data with an order by statement and to create with interrecord-functions a from-to range which may look like:

t: load
  SKU,
  if(SKU = previous(SKU), timestamp(previous(Timestamp) - maketime(0,1)), now()) as to,
  Timestamp as from
resident X order by SKU, Timestamp desc;

The next step would be to resolve the range to a dedicated timestamp with an IntervalMatch - Qlik Community - 1464547 or maybe an internal while-loop and combining the SKU + the Timestamp to create a mapping-table. With the mapping you could apply the needed conditions and wanted return-values.

[BartVA]

Thank you, great info!