topic Re: Load multiple files, compare and calculate days between dates in QlikView

Load multiple files, compare and calculate days between dates

varmekontrol — Wed, 25 Nov 2020 16:16:04 GMT

I am not sure if this is possible, but I will try to see if the Qlik hivemind has some advice/solution I can use.

I am importing multiple files in from a single folder

LOAD idNo, message, propertyNumber, currentDate FROM \\server\data\*

The files will all contain the following information

idNo	message	propertyNumber	currentDate
19667339	error message 1, 5555111	123456	2020-01-28
19667326	error message 501, 5565546	654321	2020-01-28
19667327	error message 99, 2342342	112233	2020-01-28

The next file that is generated every week, could possibly contain the same information, with only the difference in the currentDate.

What I am looking for is a way for Qlik:

Compare all the files loaded from the folder
Find the once with the same idNo
If the idNo is the same, the look at the oldest min(currentDate) and the most recent max(currentDate)
Add values to the Load statement

So I would end up with a file looking like this:

idNo	message	propertyNumber	min(currentDate)	max(currentDate)	Days between min/max
19667339	error message 1, 5555111	123456	2020-01-21	2020-01-28	7
19667326	error message 501, 5565546	654321	2020-01-21	2020-01-28	7
19667327	error message 99, 2342342	112233	2020-01-21	2020-01-28	7

Re: Load multiple files, compare and calculate days between dates

jberna26 — Tue, 28 Jan 2020 21:38:37 GMT

Hi @varmekontrol ,

Not sure if it is what you are looking for, but after you load all the files you could do the following:

AUX:

NoConcatenate

Load

idNo,

message,

propertyNumber,

min(currentDate) as min_date,

max(currentDate) as max_date

Resident OriginalTable

Group by

idNo,

message,

propertyNumber;

Drop Table OriginalTable;

Hope it helps 😉

Regards

Re: Load multiple files, compare and calculate days between dates

Brett_Bleess — Thu, 06 Feb 2020 21:52:57 GMT

Did the post you received help you with things? If so, be sure you circle back to the thread and use the Accept as Solution button on it to give the poster credit for the help and also let other Members know it did work. If you are still working upon things, leave an update post, so folks can try to offer other potential solutions.

Regards,
Brett