It depends on the order that you join stuff. If you categories are fixed you can make a bit shorter code, but if you want to handle them dynamically, you could do something like:
Final:
Load Distinct Vendor
FROM [lib://Morgan Qlik Community:DataFiles/Raw File for testing.xlsx]
(ooxml, embedded labels, table is Sheet1);
Generic:
Generic Load Distinct
Vendor,
Ageing,
sum([Amount in local cur.]) as Amount
FROM [lib://Morgan Qlik Community:DataFiles/Raw File for testing.xlsx] (ooxml, embedded labels, table is Sheet1)
Group By Vendor, Ageing;
TablesTmp:
Load 1 as TableName AutoGenerate(0);
FOR i = NoOfTables()-1 to 0 STEP -1
Concatenate (TablesTmp)
Load
TableName($(i)) as TableName,
subfield(TableName($(i)),'.',2) as TableNameShort
AutoGenerate(1);
next
TablesTmp2:
Load
TableName,
if(right(TableNameShort,3)='Yrs',
if(left(TableNameShort,1)='>',
keepchar(TableNameShort,'0123456789')*365,
subfield(TableNameShort,' ',1)*365
),
subfield(TableNameShort,'-',1)
) as Days
resident TablesTmp where left(TableName,8)='Generic.';
drop table TablesTmp;
Tables:
NoConcatenate
Load * resident TablesTmp2 order by Days asc;
drop table TablesTmp2;
FOR i = 0 to NoOfRows('Tables')-1
Let vTable = peek('TableName',$(i),'Tables');
Trace table: $(vTable);
LEFT JOIN (Final) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
NEXT
drop table Tables;
STORE Final into [lib://Morgan Qlik Community:DataFiles/Raw File OP test.csv](txt);
