Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

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

Regards,

Jaime.

Highlighted
Creator
Creator

Hi Jaime,

 

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

 

Thanks

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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?

Highlighted
Partner
Partner

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.

Highlighted
Creator
Creator

I see. This will be very useful in future.

 

Thanks again.