LOAD FIRST AND LAST DATE

[MrKi7]

Hello everybody, I’m starting to use qlikview and therefore I’m struggling with the data load, let me explain my case:

I’m loading from several files these fields:

Data:
LOAD

     MATERIAL,
     WHAREHOUSE,

     QTY_AVAILABLE,
     DATE
FROM
[C:/…/info*.qvd]
(qvd)

;

The script runs with no error but although it has a few fields there are a lot of registries because of the Date-Whse-Mtrl combinations, and also for every month there is one file, so I decided at this moment only use the first and last date of the month from every file; I can get the first day only by adding at the end of the script:

Where WildMatch(DAY, '01*')

But I can’t figure it out the way to get also the last day available as every month could be different and even sometimes it doesn’t necessary ends with the last calendar day of that month, the question/help would be, what can I do here to get the last day on every file.

Many thanks in advance for your help

[JordyWegman]

Hi,

Try this:

MaxDateTable:
LOAD Distinct

     Month( DATE ) as Month
     Max( DATE ) as MaxDateFilter
FROM [C:/…/info*.qvd] (qvd)
Group by DATE
;

Add this to your filter: Where WildMatch(DAY, '01*') OR Exists( MaxDateFiter, DATE ).

Then drop the table with Drop Table MaxDateTable;

Jordy

Climber

[MrKi7]

Thanks a lot for your time Jordy, I tried this and I'm getting the same result , only the 1st day of the month. So I didn't drop the max date and I see that the MaxDateFilter keeps all of the dates but I thought it would have only one date-the last one.

This is the script:

MaxDateTable:
LOAD Distinct
Month( DATE ) as Month,
Max(  DATE ) as MaxDateFilter
FROM
[C:/…/info4.qvd] (QVD)
Group by DATE ;

INV4:
LOAD

MATERIAL,
WAREHOUSE,
QTY_AVAILABLE,
DATE
FROM
[C:/…/info4.qvd]
(qvd)
Where WildMatch(DATE, '01*') OR Exists (MaxDateFiter, DATE)

Am I doing something wrong?

Many thanks in advance for your help.