Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
What I want to do is the follwing:
I have a folder with qvds which are named like 09-xyz.qvd or 12-xyz.qvd
The number at the beginning is always the hour of the export, so the greater the number the later the export.
With the script below I load them all and concatenate them to one table. I need them all in one table for another calculation.
After the calculation in another table I want to filter the initial table test so that I only have the rows from the latest file which is the max of _Hour.
I tried it with the inner keep load to just keep the rows with max(_Hour) as you can see. Tried it with a small self-written data sample which worked fine. Afterwards I tried it with this for-loop and the resulting big test table (2.7 Mio rows) and without the calculation between. The filter works somehow as it just keeps 346k rows but it still has 9 and 12h rows. Am I doing any mistake here? Shouldn't it just keep the rows with 12 as _Hour because its the max?
Thanks for your help guys!
Regards,
Seb
Set vPath = C:\Test;
For each File in FileList('$(vPath)\*.qvd')
test:
Load
ArtID,
num(Left(FileBaseName(),2)) as _Hour
From $(File)
(qvd);
Next
filter:
inner keep load
ArtID,
Max(_Hour) as _Hour
resident test
group by ArticleNumber;
drop table filter;
if you want to filter by max of ArtID
inner keep load
ArtID,
Max(_Hour) as _Hour
resident test
group by ArtID;
if you only want the max of all records
inner keep load
Max(_Hour) as _Hour
resident test ;
Your filter contained a further field ArtID which probably caused that you get several max. values.
- Marcus
if you want to filter by max of ArtID
inner keep load
ArtID,
Max(_Hour) as _Hour
resident test
group by ArtID;
if you only want the max of all records
inner keep load
Max(_Hour) as _Hour
resident test ;