Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_ULG
Creator
Creator

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

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_IDLocationIndustryChanged_ByChanged_Date
3854987GermanyAutoJD12-Nov-19
3854987GermanyBioGT23-Sep-19
3854987GermanyChemGT21-Sep-19
3854987AustraliaChemMP07-Aug-19
3854987AustraliaAutoLL15-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

Labels (2)
1 Solution

Accepted Solutions
jaibau1993
Partner - Creator III
Partner - Creator III

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.

View solution in original post

6 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

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

Regards,

Jaime.

Qlik_ULG
Creator
Creator
Author

Hi Jaime,

 

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

 

Thanks

jaibau1993
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

I see. This will be very useful in future.

 

Thanks again.