Looping through files with date

[Murendeni_Baloyi]

Hi Guys, 

I am trying to loop through files and left join with other files from a different location  with the same date (each file has a date on the filenames,  e.g: Asset Weight_20220706.csv. ). 

The challenge I am facing is that somehow qlikview put one date ( the initial date) in each file. Please assist

Here's my code: 

ASSET_WEIGHT_DT:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

MAX_NUMBER:
LOAD
MAX(RECNO()) AS MAX_REP
RESIDENT ASSET_WEIGHT_DT;

LET DMAX = peek('MAX_REP', 0, 'MAX_NUMBER'); // get max number of file and use that number as a counter

DROP TABLES ASSET_WEIGHT_DT, MAX_NUMBER;

)

FOR i=0 TO '$(DMAX)'-1

//GET FILE DATES
Date:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

LET USE_DT = peek('FILE_DT',i, 'Date');

//DROP TABLE Date;

//
ASSET_WEIGHTS:
LOAD
*,
IF([New_Interfund]='SG_SMOOTH_GROSS_R','RM74',[New_Interfund]) AS New_Interfund_1;
LOAD
[P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code],
[Interfund Code],
Weight,
Source,
[Price Run Type],
IF([Interfund Code] = 'SG_SMOOTH_GROSS', 'SG_SMOOTH_GROSS_'&LEFT([Pcontrol Code],1),[Interfund Code]) AS [New_Interfund]
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Weights\Asset Weights_$(USE_DT).csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Left Join

LOAD [P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code] as New_Interfund_1,
[Interfund Code] as IFC,
Weight AS New_Weight
FROM
[\\fridbk01\FRID_FS\Landing\LND_FINARCH\Business\NewAssetWeights\SG_AW_$(USE_DT).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

//get new weight
//combine interfund fields.

ASSET_WEIGHTS_1:
LOAD
[P Status],
DATE, 
[Pcontrol Code],
if(New_Interfund_1='RM74', IFC, New_Interfund_1) as [Interfund Code],
Weight,
Source,
[Price Run Type];
LOAD
[P Status],
DATE, 
[Pcontrol Code],
IFC,
if(New_Interfund_1='RM74', Weight*New_Weight, Weight) as Weight,
New_Interfund_1,
Source,
[Price Run Type]
RESIDENT ASSET_WEIGHTS;

STORE ASSET_WEIGHTS_1 INTO \\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\New_Asset_Weights\Asset Weights_'$(USE_DT)'.csv (TXT);

//DROP TABLES ASSET_WEIGHTS,ASSET_WEIGHTS_1;

NEXT

[vinieme12 · Accepted solution]

the table Date  should be outside the for loop,

ASSET_WEIGHT_DT:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);


MAX_NUMBER:
LOAD
MAX(RECNO()) AS MAX_REP
RESIDENT ASSET_WEIGHT_DT;

LET DMAX = peek('MAX_REP', 0, 'MAX_NUMBER'); // get max number of file and use that number as a counter

DROP TABLES ASSET_WEIGHT_DT, MAX_NUMBER;




//GET FILE DATES
Date:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

FOR i=0 TO '$(DMAX)'-1

LET USE_DT = peek('FILE_DT',i, 'Date');


//DROP TABLE Date;


//
ASSET_WEIGHTS:
LOAD
*,
IF([New_Interfund]='SG_SMOOTH_GROSS_R','RM74',[New_Interfund]) AS New_Interfund_1;
LOAD
[P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code],
[Interfund Code],
Weight,
Source,
[Price Run Type],
IF([Interfund Code] = 'SG_SMOOTH_GROSS', 'SG_SMOOTH_GROSS_'&LEFT([Pcontrol Code],1),[Interfund Code]) AS [New_Interfund]
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Weights\Asset Weights_$(USE_DT).csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Left Join

LOAD [P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code] as New_Interfund_1,
[Interfund Code] as IFC,
Weight AS New_Weight
FROM
[\\fridbk01\FRID_FS\Landing\LND_FINARCH\Business\NewAssetWeights\SG_AW_$(USE_DT).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

 

//get new weight
//combine interfund fields.

ASSET_WEIGHTS_1:
LOAD
[P Status],
DATE, 
[Pcontrol Code],
if(New_Interfund_1='RM74', IFC, New_Interfund_1) as [Interfund Code],
Weight,
Source,
[Price Run Type];
LOAD
[P Status],
DATE, 
[Pcontrol Code],
IFC,
if(New_Interfund_1='RM74', Weight*New_Weight, Weight) as Weight,
New_Interfund_1,
Source,
[Price Run Type]
RESIDENT ASSET_WEIGHTS;

 

STORE ASSET_WEIGHTS_1 INTO \\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\New_Asset_Weights\Asset Weights_'$(USE_DT)'.csv (TXT);

//DROP TABLES ASSET_WEIGHTS,ASSET_WEIGHTS_1;


NEXT

[vinieme12]

an alternative script  using FOR EACH NEXT

ASSET_WEIGHT_DT:
LOAD distinct left(right(FileName(),12),8) as FILE_DT
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Mix\Asset Mix_*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

FOR each USE_DT in fieldvaluelist('FILE_DT')

//DROP TABLE Date;


//
ASSET_WEIGHTS:
LOAD
*,
IF([New_Interfund]='SG_SMOOTH_GROSS_R','RM74',[New_Interfund]) AS New_Interfund_1;
LOAD
[P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code],
[Interfund Code],
Weight,
Source,
[Price Run Type],
IF([Interfund Code] = 'SG_SMOOTH_GROSS', 'SG_SMOOTH_GROSS_'&LEFT([Pcontrol Code],1),[Interfund Code]) AS [New_Interfund]
FROM
[\\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\Asset Weights\Asset Weights_$(USE_DT).csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Left Join

LOAD [P Status],
DATE#('$(USE_DT)', 'YYYY-MM-DD') AS DATE,
[Pcontrol Code] as New_Interfund_1,
[Interfund Code] as IFC,
Weight AS New_Weight
FROM
[\\fridbk01\FRID_FS\Landing\LND_FINARCH\Business\NewAssetWeights\SG_AW_$(USE_DT).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

 

//get new weight
//combine interfund fields.

ASSET_WEIGHTS_1:
LOAD
[P Status],
DATE, 
[Pcontrol Code],
if(New_Interfund_1='RM74', IFC, New_Interfund_1) as [Interfund Code],
Weight,
Source,
[Price Run Type];
LOAD
[P Status],
DATE, 
[Pcontrol Code],
IFC,
if(New_Interfund_1='RM74', Weight*New_Weight, Weight) as Weight,
New_Interfund_1,
Source,
[Price Run Type]
RESIDENT ASSET_WEIGHTS;

 

STORE ASSET_WEIGHTS_1 INTO \\fridbk01\FRID_DEV\Landing\LND_FINARCH\Business\NewAssetWeights\New_Asset_Weights\Asset Weights_'$(USE_DT)'.csv (TXT);

//DROP TABLES ASSET_WEIGHTS,ASSET_WEIGHTS_1;


NEXT

[Murendeni_Baloyi]

Thank you so much.  Qlik Community is always helpful