Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lastest value of fields

Hi All,

I would like to put the latest value of a field during the load.

I have sales record :

YYYY, MM, Product, Customer,SalesPerson, SalesTerritory, SalesGroup

and I would like to update these record during loading script to update SalesTerritoty, SalesGroup field with their latest value so that we don't keep history.

Thanks for your help 😉

1 Solution

Accepted Solutions
john_duffy
Partner - Creator III
Partner - Creator III

Hello.

Since you are now obtaining the values for Sales Rep and Sales Territory for the max YYYYMM at the customer level, you do not want to use YYYYMM in your final join. You need to do an inner join to the main table with both YYYYMM and Customer to obtain the Sales Rep and Sales Territory values for each customer. Then you left join these values to the original table. I think the following code will give you the results you are looking for:

Temp_Test_Table:
LOAD * INLINE [
YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 10, 2, 3, 1
200902, 11, 3, 3, 1
201001, 14, 2, 4, 2
] ;


Temp_Max_YYYYMM_Table:

LOAD CUSTOMER,
MAX(YYYYMM) as YYYYMM
Resident Temp_Test_Table
group by CUSTOMER
;

Max_YYYYMM_Table:
Load YYYYMM,
CUSTOMER,
SALESREP,
SALESTERRITORY
Resident Temp_Test_Table
;

inner join

LOAD YYYYMM,
CUSTOMER
Resident Temp_Max_YYYYMM_Table
;

Final_Table:
LOAD YYYYMM,
CUSTOMER,
PRODUCT
Resident Temp_Test_Table
;

left join

LOAD
CUSTOMER,
SALESREP,
SALESTERRITORY
Resident Max_YYYYMM_Table
;

Drop Tables Temp_Test_Table, Temp_Max_YYYYMM_Table, Max_YYYYMM_Table;

View solution in original post

10 Replies
john_duffy
Partner - Creator III
Partner - Creator III

Hello.

I'm assuming the lastest value would be determined by the maximum YYYY MM for each Product, Customer and SalesPerson. If so, try the following:

Load YYYY& MM as YYYYMM, Product, Customer, SalesPerson, SalesTerritory, SalesGroup

from TableA;

inner join

Load Product, Customer, SalesPerson, Max(YYYY&MM) as YYYYMM

from TableA;

Not applicable
Author

Thank you John but it does not update the field SalesTerritory, SalesGroup and create a syn table.

john_duffy
Partner - Creator III
Partner - Creator III

Can you post a simple version of the applicaion using an inline load with a description of what you would like the resulting table to look like. Where are the new values for SalesTerritory and SalesGroup obtained?

Not applicable
Author

I have the following data

LOAD * INLINE [
YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 10, 2, 3, 1
200902, 10, 2, 3, 1
201001, 14, 2, 4, 2
];

And I would like to have


YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 14, 2, 3, 2
200902, 14, 2, 3, 2
201001, 14, 2, 4, 2

Only field SALESREP, SALESTERRITORY should take the latest value (we don't want history)

john_duffy
Partner - Creator III
Partner - Creator III

The following code will give you the desired results. I am assuming that the only field used to determine which Salesrep and Salesterritory to use is YYYYMM. Let me know if this helps:











Temp_Test_Table:
LOAD * INLINE [
YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 10, 2, 3, 1
200902, 10, 2, 3, 1
201001, 14, 2, 4, 2
] ;


Max_YYYYMM_Table:

LOAD MAX(YYYYMM) as MAX_YYYYMM
Resident Temp_Test_Table
;

let vMaxYYMM = peek ('MAX_YYYYMM',0,Max_YYYYMM_Table) ;

Final_Values:
Load YYYYMM, SALESREP, SALESTERRITORY
Resident Temp_Test_Table
WHERE
YYYYMM = $(vMaxYYMM)
;

Test_Table:
Load YYYYMM, CUSTOMER, PRODUCT
Resident Temp_Test_Table
;

left join

Load SALESREP, SALESTERRITORY
Resident Final_Values
;

Drop Tables Temp_Test_Table, Max_YYYYMM_Table, Final_Values ;







montubhardwaj
Specialist
Specialist

Nice logic John...!!!

Not applicable
Author

Thanks John. I think it will help. I use 2 fields to determine salesrep and territory : customer and YYYYMM

Not applicable
Author

Hi John,

I tried this but it does not work nothing is in Final result.

Can you help me ?

Temp_Test_Table:
LOAD * INLINE [
YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 10, 2, 3, 1
200902, 11, 3, 3, 1
201001, 14, 2, 4, 2
] ;


Max_YYYYMM_Table:

LOAD MAX(YYYYMM) as YYYYMM,
CUSTOMER
Resident Temp_Test_Table
group by CUSTOMER;


Final_Values:

LOAD YYYYMM,
CUSTOMER
Resident Max_YYYYMM_Table;
inner join
LOAD YYYYMM,
CUSTOMER,SALESREP, SALESTERRITORY
Resident Temp_Test_Table;

Drop Tables Temp_Test_Table;

john_duffy
Partner - Creator III
Partner - Creator III

Hello.

Since you are now obtaining the values for Sales Rep and Sales Territory for the max YYYYMM at the customer level, you do not want to use YYYYMM in your final join. You need to do an inner join to the main table with both YYYYMM and Customer to obtain the Sales Rep and Sales Territory values for each customer. Then you left join these values to the original table. I think the following code will give you the results you are looking for:

Temp_Test_Table:
LOAD * INLINE [
YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
200901, 10, 2, 3, 1
200902, 11, 3, 3, 1
201001, 14, 2, 4, 2
] ;


Temp_Max_YYYYMM_Table:

LOAD CUSTOMER,
MAX(YYYYMM) as YYYYMM
Resident Temp_Test_Table
group by CUSTOMER
;

Max_YYYYMM_Table:
Load YYYYMM,
CUSTOMER,
SALESREP,
SALESTERRITORY
Resident Temp_Test_Table
;

inner join

LOAD YYYYMM,
CUSTOMER
Resident Temp_Max_YYYYMM_Table
;

Final_Table:
LOAD YYYYMM,
CUSTOMER,
PRODUCT
Resident Temp_Test_Table
;

left join

LOAD
CUSTOMER,
SALESREP,
SALESTERRITORY
Resident Max_YYYYMM_Table
;

Drop Tables Temp_Test_Table, Temp_Max_YYYYMM_Table, Max_YYYYMM_Table;