Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 3 excel files with same column name but different data ..
so i need to concatentate those tables or join those tables ?
when i try first excel files i do this
LOAD
ID,
HostID,
HostName,
HostSince,
HostResponseTime
FROM [lib://b-attachments/data.xlsx]
(ooxml, embedded labels, table is Jandata);
then on script on another section i do this
Left Join(Jandata)
Load HostID,
Count(ID) as NoList
Resident Jandata
Group by HostID;
so now i have 2 more excel files .. how i do that on script ?
I would probably concatenate the three tables first - additionally adding a source-information manually or maybe with something like filebasename() - and then joining the count to it (if the source is relevant for the count you need to add this field to the join-load, too.
- Marcus
Try something like this below. First join then populate your final table.
FOR each _file in list 'data.xlsx', 'data2.xlsx', 'data3.xlsx'
TMP:
LOAD
ID,
HostID,
HostName,
HostSince,
HostResponseTime,
'AutoconcatenateStopper' as DummyField
FROM [lib://b-attachments/$(_file)]
(ooxml, embedded labels, table is Jandata);
Left Join(TMP)
Load HostID,
Count(ID) as NoList
Resident TMP
Group by HostID;
//Autoconcatenate
Final:
LOAD
ID,
HostID,
HostName,
HostSince,
HostResponseTime,
NoList
RESIDENT TMP;
DROP TABLE TMP
NEXT _file
i did not understand this line
FROM [lib://b-attachments/$(_file)]
why you write this $(_file) instead of excel file name ?
I was looping through your three files that I named 'data.xlsx', 'data2.xlsx' and 'data3.xlsx'. For each iteration the content of the variable _file will change according to the list.
FOR each _file in list 'data.xlsx', 'data2.xlsx', 'data3.xlsx'
TMP:
LOAD
...
FROM [lib://b-attachments/$(_file)]
(ooxml, embedded labels, table is Jandata);
NEXT _file