Load last version of data only

[cjliew]

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?):

Expected result (Only show the highlighted row):

[avinashelite · Accepted solution]

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]

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.

[cjliew]

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]

@avinashelite  Yes, it is the result I want.

Many thanks for your support.

[alkoni]

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;