topic Remove data in script in QlikView

Remove data in script

Tue, 02 Aug 2011 05:37:49 GMT

Dear all,

I'm new to qlikview and I would like help with a problem I have encountered. In my script I’m loading data from a single textfile with orderlines that are delivered on acertain date.

The textfile contains data like below:

Deliveries:

LOAD

[Material number],

[Material description],

[Order no],

Item,

([Order no]&Item) as ItemID,

[GR date]

FROM

$(vPathQvdRaw)\Data\Deliveries.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t');

The ItemID is a unique number for each orderline. But then one ItemID can be delivered on different occasions. I only want to keep the ItemID with the latest [GR date]. For this I usually sort my data in excel so that I have ItemID ascending and the latest [GR date] on the top row and then all the other data below. Then I check if the ItemID on one row is the same as the previous one and if it is I mark this line with an x. Then I just delete all the rows marked with an x.

How can I do this in qlikview?

Would very much appreciate your help.

Thanks in advance,

Martin

Remove data in script

rahulgupta — Tue, 02 Aug 2011 05:54:54 GMT

Hey hi,

Use this code:

LOAD [Material number],
[Material description],
[Order no],
Item,
([Order no]&Item) as ItemID,
[GR date]
FROM
$(vPathQvdRaw)\Data\Deliveries.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t');

LEFT JOIN

LOAD ([Order no]&Item) as ItemID,
mAX([GR date]) AS [Max GR Date]
FROM
$(vPathQvdRaw)\Data\Deliveries.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t')
Group By [Order no]&Item;

Now, Take the [Max GR Date] field instead of [GR Date] in the chart.

Moreover, if you want to keep only the Max Values to exist than Use Right Join Instead of Left Join.

Hope this helps...

Regards

Remove data in script

Tue, 02 Aug 2011 06:41:03 GMT

Hello,

Thanks for that very fast answer. Why do I get 'Field not found' when I'm trying to use the Max GR Date to transformit into a datekey (which I use in a calender)?

Martin

Remove data in script

Tue, 13 Sep 2011 12:06:25 GMT

Hi all,

Thanks for the help above. When the script loads the data, the number of orderlines that are extracted is correct. What I now want to do is to only keep the rows that are defined and scrapt all the other ones.

How can I do this?

Thanks in advance,

Best regards

Martin