Load multiple files, compare and calculate days between dates

[varmekontrol]

Hi

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

[jberna26]

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

[Brett_Bleess]

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