topic Re: Putting condition while loading from an excel file in QlikView

Putting condition while loading from an excel file

bimala0507 — Thu, 27 Mar 2014 05:45:41 GMT

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

Re: Putting condition while loading from an excel file

Thu, 27 Mar 2014 06:07:33 GMT

Newtable:

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

resident <table>

group by

<field>;

Re: Putting condition while loading from an excel file

kumarnatarajan — Thu, 27 Mar 2014 06:08:14 GMT

Hi BIM,

You can get using group by function.

EX:

Load

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

group by ID,NO;

Re: Putting condition while loading from an excel file

bimala0507 — Thu, 27 Mar 2014 06:18:49 GMT

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

Re: Putting condition while loading from an excel file

Thu, 27 Mar 2014 06:25:26 GMT

That is what firsortedvalue actually does.

Sent from my iPhone

Re: Putting condition while loading from an excel file

bimala0507 — Thu, 27 Mar 2014 06:40:26 GMT

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

Sent from my iPhone

Re: Putting condition while loading from an excel file

kumarnatarajan — Thu, 27 Mar 2014 07:51:07 GMT

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;

Re: Putting condition while loading from an excel file

arsal_90 — Thu, 27 Mar 2014 07:58:17 GMT

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;

Re: Re: Putting condition while loading from an excel file

arsal_90 — Thu, 27 Mar 2014 08:14:42 GMT

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

Re: Re: Putting condition while loading from an excel file

bimala0507 — Thu, 27 Mar 2014 15:08:34 GMT

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$);

Re: Re: Putting condition while loading from an excel file

bimala0507 — Thu, 27 Mar 2014 15:17:11 GMT

Hi:

This is really good way of handling this scenario. Thanks a lot.

Thanks & Regards,

Bimala

Re: Re: Putting condition while loading from an excel file

arsal_90 — Thu, 27 Mar 2014 16:24:24 GMT

Ok Bimla So can u refer this post as answer for other people reference

Thanks & regards

MUHAMMAD