Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Fields comparison of multiple files

Hello everybody.
I don't know if this question is already answered but my searches stayed unsuccessful, so I need help, please.
I have to compare a date field value of multiple Excel files.
It work like this :
I load date field from Excel files with wildcard and this field (date) can change on every Excel file.
FROM
Every Excel file in named xxx_dd-mm-yyy.xls, and I make a field date_export with each filename :
Date(Date#(Right(FileBaseName(), 10), 'DD-MM-YYYY')) As Date_export
So for 1 date_export line I have 1 field date as Besoin_CMD_Corrige
How can I compare the Besoin_CMD_Corrige of the the last Excel file with the last -1 as a new fiels ?
I need : if(Besoin_CMD_Corrige of max(date_export) < Besoin_CMD_Corrige of max(date_export) -1,'earlier deadline',null()) but I don't know how ti implement the "of".
Community, thanks in advance.
Jérôme
2 Replies
datanibbler
Esteemed Contributor

Re: Fields comparison of multiple files

Hi Jerome,

I am not quite clear what your scenario is, but going by what you say, you just need to draw that date ("Besoin_CMD_Corrige") from the record with the largest date?

That's rather easy:

- Sort your table (only possible in a RESIDENT LOAD, not in the primary LOAD) by that date in asc. order

=> The largest date will be the very last record.

- Then use the PEEK() function to get that date into a variable - without a number as parameter (only the fieldname as parameter), it automatically uses the record that was loaded the very last - that would then be the one with the largest date.

=> You can use that variable for your comparison.

HTH.

Best regards,

DataNibbler

Not applicable

Re: Fields comparison of multiple files

Hi DataNibbler.

I try the Peek() solution and it work but not 100% for me.

When I have a single valur for "Besoin_CMD_Corrige" field, the peek() function return a wrong date but when It is not single that's work.

Also I only success with the Peek() function when I use it in a chart as expression, not in the script.

Best regards Jérôme

Community Browser