Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi:
I am trying to load an excel file into qlikview. While loading, I have some condition to satisfied e.g. pick up the recod with Maximum PO_date.
E.g
If I have records like as given below
BATT_SPC1520 | E0001AU | 4500020067 | 1/9/2014 | 10 | 2.76 |
BATT_SPC1520 | E0038US | 4500020067 | 1/8/2014 | 10 | 2.76 |
BATT_SPC1520 | E0039US | 4500020067 | 1/7/2014 | 10 | 2.76 |
I would like to load only the record maximum po_date i.e. here the first row.
I think I should go for precendent load option, but not sure how to go about it.
Any quick help would be higly appreciated.
Rgds,
Bimala
Newtable:
LOAd firsortedvalue(<field>,-date) as latestfield
resident <table>
group by
<field>;
Hi BIM,
You can get using group by function.
EX:
Load
ID,NO,MAX(Date) from ect....
group by ID,NO;
Hi:
Thanks , but this will not work out, it will consider each row as unique and
Will evaluate for each row. I need to pick up only the row with max date group by only Id, not the other columns.
Thanks & Regards,
Bimala
That is what firsortedvalue actually does.
Sent from my iPhone
I am trying with first sorted value also, but not getting me the desired result.
Sent from my iPhone
Hi,
Try below script.
Ex:
Temp:
LOAD * INLINE [
Name, F2, NO, Date, F5, F6
BATT_SPC1520, E0001AU, 4500020067, 1/9/2014, 10, 2.76
BATT_SPC1520, E0038US, 4500020067, 1/8/2014, 10, 2.76
BATT_SPC1520, E0039US, 4500020067, 1/7/2014, 10, 2.76
];
Temp2:
NoConcatenate
LOAD * Where A_No=1;
LOAD *,AutoNumber(Date,NO) as A_No Resident Temp Order by NO,Date desc;
DROP Table Temp;
A:
LOAD name1,
[po-num],
name2,
po_date,
qty,
value,
0 as Flag
FROM
[..\Documents\Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Right join
Load [po-num],
qty,
firstsortedvalue(name2,-po_date) as name2,
value,
name1,
1 as Flag
Resident A
Group By [po-num],qty,
value,name1;
Please find the attached file for your desired result. if any problem then let me know
Thanks much Muhammad: I have a personal edition , so could not open your qvw. However I tried some other way and achieved the result. Just attaching for your reference.
MAX_PO_DT_2013:
LOAD MATNR ,
MAX(Date([EKKO.DATE BEDAT])) AS PO_DT_2013
FROM
(
GROUP BY MATNR;
INNER JOIN(MAX_PO_DT_2013)
LOAD MATNR,
EKKO.LIFNR as Vendor_2013,
EBELN as PO_2013,
date([EKKO.DATE BEDAT]) as PO_DT_2013,
EKPO.EBELP as po_item_2013,
[EKPO.NETPR ] as net_price_2013,
[EKPO.PEINH ]as price_unit_2013,
EKKO.WAERS as curr_2013
FROM
(