topic Lastest value of fields in QlikView

Lastest value of fields

Thu, 26 Aug 2010 04:23:26 GMT

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 😉

Lastest value of fields

john_duffy — Thu, 26 Aug 2010 13:54:59 GMT

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;

Lastest value of fields

Thu, 26 Aug 2010 15:47:54 GMT

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

Lastest value of fields

john_duffy — Thu, 26 Aug 2010 15:55:15 GMT

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?

Lastest value of fields

Thu, 26 Aug 2010 16:20:56 GMT

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)

Lastest value of fields

john_duffy — Thu, 26 Aug 2010 17:15:16 GMT

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 ;

Lastest value of fields

montubhardwaj — Thu, 26 Aug 2010 17:42:11 GMT

Nice logic John...!!!

Lastest value of fields

Thu, 26 Aug 2010 17:44:17 GMT

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

Lastest value of fields

Fri, 27 Aug 2010 09:40:11 GMT

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;

Lastest value of fields

john_duffy — Fri, 27 Aug 2010 13:25:22 GMT

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;

Lastest value of fields

Sun, 29 Aug 2010 09:16:19 GMT

Thank you John ! It is working