Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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 Smiley Wink

Tags (1)
1 Solution

Accepted Solutions
john_duffy
Contributor III

Lastest value of fields

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;

10 Replies
john_duffy
Contributor III

Lastest value of fields

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

Lastest value of fields

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

john_duffy
Contributor III

Lastest value of fields

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

Lastest value of fields

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
Contributor III

Lastest value of fields

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
Valued Contributor

Lastest value of fields

Nice logic John...!!!

Not applicable

Lastest value of fields

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

Not applicable

Lastest value of fields

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
Contributor III

Lastest value of fields

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;