Putting condition while loading from an excel file

[bimala0507]

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

[#2]

Newtable:

LOAd firsortedvalue(<field>,-date) as latestfield

resident <table>

group by

<field>;

[kumarnatarajan]

Hi BIM,

You can get using group by function.

EX:

Load

ID,NO,MAX(Date) from ect....

group by ID,NO;

[bimala0507]

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

[#5]

That is what firsortedvalue actually does.

Sent from my iPhone

[bimala0507]

I am trying with first sorted value also, but not getting me the desired result.

Sent from my iPhone

[kumarnatarajan]

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;

[arsal_90]

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;

[arsal_90]

Please find the attached file for your desired result. if any problem then let me know

[bimala0507]

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

(biff, embedded labels, table is Sheet1$)
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
(biff, embedded labels, table is Sheet1$);