topic Re: Fields comparison of multiple files in QlikView

Fields comparison of multiple files

Thu, 18 Dec 2014 15:00:03 GMT

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

Re: Fields comparison of multiple files

datanibbler — Thu, 18 Dec 2014 15:10:53 GMT

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

Re: Fields comparison of multiple files

Fri, 19 Dec 2014 14:55:48 GMT

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