@gauravs275 It can be tricky with preceeding loads in combination with LOAD ... FROM *, you will often end up with multiple "identical" tables.
I would suggest you try to change your FROM wildchar* script into a loop that reads all files one at the time.
for each vFile in FileList('C:\Users\Administrator\Desktop\TRN_BI_KPI_CP2022_PastData\TRN_BI_KPI_CP2022_*.tsv')
[Transaction]:
LOAD ... ;
LOAD ... ;
LOAD ... ;
LOAD
[Year Id]&'-'&[Reporting Month Id]&'-'&[Block Id]&'-'&[Pillar Id]&'-'&[KPI Id] as %Key,
[Year Id],
Date(Date#([Reporting Month Id],'M'),'MMM') AS [Reporting Month],
[Block Id],
[Pillar Id],
[KPI Id],
[Country Code] AS Market,
[Latest Flag],
Sum([Target Amount]) AS [Target Amount],
Sum([Actual Amount]) AS [Actual Amount],
RowNo() as RowId
FROM
[$(vFile)]
(txt, unicode, embedded labels, delimiter is '\t', msq)
where
match([Latest Flag],1)
Group By
[Year Id],
[Reporting Month Id],
[Block Id],
[Pillar Id],
[KPI Id],
[Country Code],
[Latest Flag];
Next vFile
You will probably get trouble with the LEFT JOIN FROM Wildchar* as well so it is better to first create and fill the table you want to use for the join before executing the JOIN command.
/*Creating the table you want to use for join*/
TMP:
LOAD
[Year Id]&'-'&[Reporting Month Id]&'-'&[Block Id]&'-'&[Pillar Id]&'-'&[KPI Id] as %Key,
[Root Cause],
Countermeasure,
[KPI Result Forecast],
RowNo() as RowId2 //You will probably not want to join on ths field name
FROM
[C:\Users\Administrator\Desktop\TRN_BI_KPI_CP2022_PastData\TRN_BI_KPI_CP2022_*.tsv]
/*When all data is collected then do the join*/
LEFT Join (Transactions)
LOAD * Resident TMP;
/*Drop the TMP as you don't need it in your data model*/
Drop Table TMP;
Good luck with solving you problem.
If these suggestions nor @Brett_Bleess links doesn't help you, then you will probably need to be more specific and share more details in order to get the community to help you.
.png "Former Employee"){kind=icon}
