Hi qlik community.
I have folders and each folder name is a date. Each folder has a file in it. I want to loop through each file and add a date column in each file but when looping current file I want the date to be date of the previous folder not current folder. For example, if I have two folders with named 20220815 and 20220816, the date that will be on the column for file inside the 20220816 folder should be 20220815.
Here is my code to achieve that, concatenating the files work but getting date of the previous file on current one is not working. Please help
SUB GetValueUnits (dir)
FOR EACH file in filelist('$(dir)' & '\' & '$(FILE_ID)' & '.csv') ;
vFileNo=$(vFileNo)+1;
IF $(vFileNo)=1 then
VALUE_OF_UNITS:
LOAD PRODUCT,
PORTFOLIO,
Date('$(vExtract_date)', 'YYYY-MM-DD') as EXTRACT_DATE,
NO_OF_UNITS,
UNIT_PRICE,
VALUE_OF_UNITS
FROM
'$(file)'
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
ELSE
LOAD PRODUCT,
PORTFOLIO,
Date('$(vExtract_date)', 'YYYY-MM-DD') as EXTRACT_DATE,
NO_OF_UNITS,
UNIT_PRICE,
VALUE_OF_UNITS
FROM
'$(file)'
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
END IF ;
NEXT file;
SET file=;
END SUB
SUB doDir (root)
vFileNo=0;
FILE_ID = 'Value of Units';
vstartdate= num(MakeDate(2022,08,14));
FOR EACH subdir in dirlist( '$(root)'& '\*' )
vDirType=right('$(subdir)',8) //folder name = folder date
IF isnum(vDirType)=-1 then
vDirDate=num(makedate(left('$(vDirType)',4),mid('$(vDirType)',5,2),mid('$(vDirType)',7,2))) //making file date to be datetype
IF '$(vDirDate)'>'$(vstartdate)' then //getting files from only two months back
//getting file dates here
FILEDATES:
LOAD ROWNO() AS FILE_NO,
NUM(RIGHT('$(subdir)',8)) as DATES
AUTOGENERATE 1;
LET NumRows=NoOfRows('FILEDATES');
FOR i=-1 to $(NumRows)
LET vDate=FieldValue('DATES',$(i));
vExtract_date=num(makedate(left('$(vDate)',4),mid('$(vDate)',5,2),mid('$(vDate)',7,2)))
NEXT;
CALL GetValueUnits('$(subdir)')
END IF
END IF
NEXT subdir;
END SUB
