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

Load last version of data only

Dear All, 

I would like to load in only the last version of the data, any idea how the scripting should be?

I tried the scripting below, but result is not as expected (The value is total of 4 versions?):

cjliew_1-1602042830871.png

cjliew_2-1602042933515.png

 

 

Expected result (Only show the highlighted row):

cjliew_0-1602042751278.png

 

Labels (1)
  • SaaS

1 Solution

Accepted Solutions
avinashelite

You need to do it in 2 steps . Try like this
Temp:
LOAD 
Max("Quotation Version")&'-'&"Quotation No."&'-'& "Sales Order No."  as "Latest_Version_Key"
Resident
List_Price
Group by
"Quotation No.",
"Sales Order No.";
Noconcatenate
Final_Data:
LOAD
*
Resident
List_Price
Where Exists (Latest_Version_Key,"Quotation Version"&'-'&"Quotation No."&'-'&"Sales Order No.");

View solution in original post

7 Replies
avinashelite

You need to do it in 2 steps . Try like this
Temp:
LOAD 
Max("Quotation Version")&'-'&"Quotation No."&'-'& "Sales Order No."  as "Latest_Version_Key"
Resident
List_Price
Group by
"Quotation No.",
"Sales Order No.";
Noconcatenate
Final_Data:
LOAD
*
Resident
List_Price
Where Exists (Latest_Version_Key,"Quotation Version"&'-'&"Quotation No."&'-'&"Sales Order No.");

alkoni
Contributor II
Contributor II

Hi All, First you have to load the main table say for example, QuotationVersion and then create a Temp table from Resident of QuotationVersionPrice table then Inner Join By OrderNo with the table created from Resident of Temp which was newly created table and then finally drop the QuotationVersionPrice table as per the below script:


QuotationVersionPrice :
LOAD
[OrderNo],
[QuotationNo],
[QuotationVersion],
[Sales]
FROM [lib://AttachedFiles/QuotationVersion.xlsx]
(ooxml, embedded labels, table is Sheet1);


Temp:
Load
max(QuotationVersion) as LatestVersion,
max(Sales) as LatestSales,
QuotationNo,
OrderNo
Resident QuotationVersionPrice
group by QuotationNo,OrderNo;

inner join(Temp)
Load
OrderNo,
max(LatestVersion) as LatestVersion,
max(LatestSales) as LatestSales
Resident Temp
group by OrderNo;

drop table QuotationVersionPrice;

Exit Script;
Hope you are clear now, Thanks...

sorry, now attaching sample QuotationVersion.xlsx file in response to Load last version of data only.

alkoni
Contributor II
Contributor II

 
cjliew
Contributor III
Contributor III
Author

Hi, but my Sales is not keep increasing based on version. It could be lower at latest version. If I use Max(Sales), will it capture the maximum sales amount only?

avinashelite

Did you tried by solution ?? it will cater your requirement whether increase or decrease of the sales ...

cjliew
Contributor III
Contributor III
Author

@avinashelite  Yes, it is the result I want.

Many thanks for your support.

alkoni
Contributor II
Contributor II

I have achieved this by script twiking using Applymap (for better performance if data volume is huge), and FirstSortedValue function in chart table in Set Analysis  as   =FirstSortedValue(Sales,-Latest_Version_Key)  .

QuotationVersionPrice :
LOAD
[OrderNo],
[QuotationNo],
[QuotationVersion],
[Sales]
FROM [lib://AttachedFiles/QuotationVersion.xlsx]
(ooxml, embedded labels, table is Sheet1);

Temp:
Load
AutoNumber(max("QuotationVersion"))&''&AutoNumber(QuotationNo)&''& AutoNumber(OrderNo) as "Latest_Version_Key",
Sales
Resident QuotationVersionPrice
group by AutoNumber(QuotationNo),AutoNumber(OrderNo),Sales;

Map1:
mapping load "Latest_Version_Key",Sales
resident Temp;

right join(Temp)

HighestVersion:
Load
Latest_Version_Key,
ApplyMap('Map1', Latest_Version_Key) as Sales2,
max(Latest_Version_Key) as Latest_Version_Key1,
RecNo() as Latest_Version
Resident Temp
Group By Latest_Version_Key,ApplyMap('Map1', Latest_Version_Key);

Drop table QuotationVersionPrice;

Exit Script;