Return only values related to first instance of current value of a given field

[Qlik_ULG]

Hello,

This problem seems relatively straightforward conceptually, but I haven't been able to get this working as needed.

I have a Customer history table which stores all records of any changes made to field values in the Customer table. For the purpose of this development, I'm interested only in the values related to the last time one particular field change.

In the example below, I'm basically trying to load the Changed_By and Changed_Date fields for the last instance where the Location value was changed (i.e. GT and 21-Sep-19):

Customer_ID	Location	Industry	Changed_By	Changed_Date
3854987	Germany	Auto	JD	12-Nov-19
3854987	Germany	Bio	GT	23-Sep-19
3854987	Germany	Chem	GT	21-Sep-19
3854987	Australia	Chem	MP	07-Aug-19
3854987	Australia	Auto	LL	15-Apr-19

I have tried FirstSortedValue  and a few variations using Peek, but have not been able to get the desired result.

Any help would be greatly appreciated.

Thanks very much

[jaibau1993]

Hi! Do you need to perform it in the script side?

Regards,

Jaime.

[Qlik_ULG]

Hi Jaime,

Yes, I'm trying to define these in the load script.

Thanks

[jaibau1993 · Accepted solution]

Then try something as the following:

Source:
LOAD
	Customer_ID,
	Location,
	Industry,
	Changed_By,
	Date#(Changed_Date, 'DD-MMM-YY') as Changed_Date
;
LOAD * INLINE [
    Customer_ID, Location, Industry, Changed_By, Changed_Date
    3854987, Germany, Auto, JD, 12-nov-19
    3854987, Germany, Bio, GT, 23-sep-19
    3854987, Germany, Chem, GT, 21-sep-19
    3854987, Australia, Chem, MP, 07-Aug-19
    3854987, Australia, Auto, LL, 15-Apr-19
    3854987, Spain, Auto, LL, 14-Apr-19
    OtherID, Australia, Auto, LL, 15-Apr-19
    OtherID, Spain, Auto, LL, 14-Apr-19
];

Modified:
NoConcatenate LOAD
	Customer_ID,
	
	LastValue(Changed_By) as Changed_By,
	LastValue(Changed_Date) as Changed_Date //Finally, I group by Customer_ID and pick up the last value (it is already sorted)
Where ToLoad
Group By Customer_ID
;
LOAD
	Customer_ID,
	Location,
	Industry,
	Changed_By,
	Changed_Date,
	if(Previous(Location) <> Location and Previous(Customer_ID) = Customer_ID, 1, 0) as ToLoad	//I Create a flag that allows me to only keep rows when Location is modified for a given Customer_ID
Resident Source //You MUST use a resident LOAD because you need to "Order by" and it only works on resident!
Order By
Customer_ID, Changed_Date asc
;

DROP Table Source;

Regards,

Jaime.

[Qlik_ULG]

Thanks, Jaime.

That solved the issue perfectly. Very much appreciated.

One question. I've never used "ToLoad".  Can you confirm what it is performing in this context?

[jaibau1993]

Hi! I'm glad it helped!

"ToLoad" is an auxiliar field that I create to write the where statement comfortably. This field contains the value 1 if the Location changes but Customer_ID doesn't and the value 0 otherwise (it is a flag). After this I perform a "Where ToLoad" which is equivalent to "Where ToLoad = 1", thus I keep only those rows where Location has changed within a given Customer_ID.

Regards,

Jaime.

[Qlik_ULG]

I see. This will be very useful in future.

Thanks again.