Remove data in script

[#1]

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

[rahulgupta]

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

[#3]

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

BR

Martin

[#4]

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