Mutiple excel's need week invoice

Surya — Sat, 16 Nov 2024 03:14:00 GMT

Dear Sir,

I facing some issue

i loaded multiple invoice excels into single load

every excel having same table but invoice data is different

in excel names like this

01-04-2019
01-05-2019
01-06-2019
02-04-2019
02-05-2019
03-04-2019
03-05-2019
03-06-2019
04-04-2019
04-05-2019
04-06-2019
05-04-2019
05-06-2019
06-04-2019
06-05-2019
06-06-2019
07-04-2019
07-05-2019
07-06-2019
08-04-2019
08-05-2019
08-06-2019
09-04-2019
09-05-2019
09-06-2019
10-04-2019
10-05-2019
10-06-2019
11-04-2019
11-05-2019
11-06-2019
12-04-2019
12-05-2019
12-06-2019
13-04-2019
13-05-2019
13-06-2019
14-04-2019
14-05-2019
14-06-2019
15-04-2019
15-05-2019
15-06-2019
16-04-2019
16-05-2019
16-06-2019
17-04-2019
17-05-2019
17-06-2019
18-04-2019
18-05-2019
18-06-2019
19-04-2019
19-05-2019
19-06-2019
20-04-2019

in this directly weeks hardcoded like 1 from 1st week 8 come 2nd 16 come third 24 come 4th

now for example today date is 20th June so 3rd week having 16th and after 17-20 days also user want upto 24

when 24 is coming they taken 4th week ..remove in middle 17-23 like script

let vDataFolder = 'C:\User 4\Groupwise inventory\';

// enumerate files

for each vFile in filelist('$(vDataFolder)*.xlsx')

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

NEW:

LOAD *,

'$(vFileName)' as [File Name],

FileBaseName('$(vFileName)') as filebasename

// '$(vSheetName)' as [Sheet Name]

FROM [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

//NoConcatenate

load * ,

IF(WILDMATCH(filebasename,'01-04-20*'),'April1st',

IF(WILDMATCH(filebasename,'08-04-20*'),'April2nd',

IF(WILDMATCH(filebasename,'16-04-20*'),'April3rd',

IF(WILDMATCH(filebasename,'24-04-20*'),'April4th',

IF(WILDMATCH(filebasename,'01-05-20*'),'May1st',

IF(WILDMATCH(filebasename,'08-05-20*'),'May2nd',

IF(WILDMATCH(filebasename,'16-05-20*'),'May3rd',

IF(WILDMATCH(filebasename,'24-05-20*'),'May4th',

IF(WILDMATCH(filebasename,'01-06-20*'),'June1st',

IF(WILDMATCH(filebasename,'08-06-20*'),'June2nd',

IF(WILDMATCH(filebasename,'16-06-20*'),'June3rd',

IF(WILDMATCH(filebasename,'24-06-20*'),'June4th',

IF(WILDMATCH(filebasename,'01-07-20*'),'July1st',

IF(WILDMATCH(filebasename,'08-07-20*'),'July2nd',

IF(WILDMATCH(filebasename,'16-07-20*'),'July3rd',

IF(WILDMATCH(filebasename,'24-07-20*'),'July4th',

IF(WILDMATCH(filebasename,'01-08-20*'),'Augest1st',

IF(WILDMATCH(filebasename,'08-08-20*'),'Augest2nd',

IF(WILDMATCH(filebasename,'16-08-20*'),'Augest3rd',

IF(WILDMATCH(filebasename,'24-08-20*'),'Augest4th',

IF(WILDMATCH(filebasename,'01-09-20*'),'Sept1st',

IF(WILDMATCH(filebasename,'08-09-20*'),'Sept2nd',

IF(WILDMATCH(filebasename,'16-09-20*'),'Spet3rd',

IF(WILDMATCH(filebasename,'24-09-20*'),'Sept4th',

IF(WILDMATCH(filebasename,'01-10-20*'),'Oct1st',

IF(WILDMATCH(filebasename,'08-10-20*'),'Oct2nd',

IF(WILDMATCH(filebasename,'16-10-20*'),'Oct3rd',

IF(WILDMATCH(filebasename,'24-10-20*'),'Oct4th',

IF(WILDMATCH(filebasename,'01-11-20*'),'Nov1st',

IF(WILDMATCH(filebasename,'08-11-20*'),'Nov2nd',

IF(WILDMATCH(filebasename,'16-11-20*'),'Nov3rd',

IF(WILDMATCH(filebasename,'24-11-20*'),'Nov4th',

IF(WILDMATCH(filebasename,'01-12-20*'),'Dec1st',

IF(WILDMATCH(filebasename,'08-12-20*'),'Dec2nd',

IF(WILDMATCH(filebasename,'16-12-20*'),'Dec3rd',

IF(WILDMATCH(filebasename,'24-12-20*'),'Dec4th',

IF(WILDMATCH(filebasename,'01-01-20*'),'Jan1st',

IF(WILDMATCH(filebasename,'08-01-20*'),'Jan2nd',

IF(WILDMATCH(filebasename,'16-01-20*'),'Jan3rd',

IF(WILDMATCH(filebasename,'24-01-20*'),'Jan4th',

IF(WILDMATCH(filebasename,'01-02-20*'),'Feb1st',

IF(WILDMATCH(filebasename,'08-02-20*'),'Feb2nd',

IF(WILDMATCH(filebasename,'16-02-20*'),'Feb3rd',

IF(WILDMATCH(filebasename,'24-02-20*'),'Feb4th',

IF(WILDMATCH(filebasename,'01-03-20*'),'March1st',

IF(WILDMATCH(filebasename,'08-03-20*'),'March2nd',

IF(WILDMATCH(filebasename,'16-03-20*'),'March3rd',

IF(WILDMATCH(filebasename,'24-03-20*'),'March4th')))))))))))))))))))))))))))))))))))))))))))))))) as NewBase

Resident NEW;

drop table NEW;

i written this upto please help for extend

Re: Mutiple excel's need week invoice

Surya — Thu, 20 Jun 2019 10:57:44 GMT

RUBM
Wk/Day	FG
April-1st	11007
April-2nd	14754
April-3rd	15641
April-4th	12511
May-1st	13161
May-2nd	16354
May-3rd	17761
May-4th	16303
June-1st	15666
June-day8	16048
June-day9	16912
June-day10	15720
June-day11	16178

like this ..weeks i got but im not getting days

Re: Mutiple excel's need week invoice

Brett_Bleess — Mon, 22 Jul 2019 14:57:26 GMT

Not sure if this is any help or not, as I am not exactly sure where you are struggling here, in these cases it can be helpful if you can attach a qvw file with what you have etc., to allow folks to better see what you are doing...

https://community.qlik.com/t5/Qlik-Design-Blog/

There are hundreds of posts on the above link, do some searching there to see if you can find something that may help, I was not exactly sure where you were stuck here, so was not sure what to try to find for you.

Regards,
Brett

topic Re: Mutiple excel's need week invoice in QlikView

Mutiple excel's need week invoice

Re: Mutiple excel's need week invoice

Re: Mutiple excel's need week invoice