Load table with same content but different column names

[MindaugasBacius]

Hello, Community,

I am facing an issue with the column names.

I have tens of XML files with 3 fields:

- 2 of the fields are named the same

-the third always differs but have the same start %Key_GenericData_

tmp:
LOAD id,
value,
%Key_GenericData_FDE8B6A546EBA8C1 // Key to parent table: GenericData
FROM [...\Eksportas\*xml] (XmlSimple, Table is [GenericData/DataSet/Obs/ObsKey/Value]);

How could I concatenate all XML files into one table with 3 same column names?

Thank you!

[hic]

I cannot think of a simple automatic way to do this, but one solution could be to have an "Alias" statement in the beginning of the script:

Alias
%Key_GenericData_FDE8B6A546EBA8C1 as %Key,
%Key_GenericData_FDE8B63786EBA844 as %Key,
%Key_GenericData_FDE8B6A546E235KJ as %Key,
%Key_GenericData_FDE8B6A54634DE12 as %Key;

The advantage would be that you then don't need to change the individual Load statements. The disadvantage would be that you need to manage the Alias statement manually.

[MindaugasBacius]

Henric_Cronström , the other challenge is that the files are added every single month. The number of unique key columns is growing constantly.

Not so sure how to grab those not manually at the moment. Any thoughts on that?

[vinieme12 · Accepted solution]

Here's an Idea, Loop thru All the files

1) load some data

2) loop thru available field names to create a string variable which will be a replacement for the Load statement

              2.1) if the fieldname is not in the list of the two expected fieldname then add and alias

              2.1.1 goal is to create a string to be used in load statement

                          finalstring  =             ID,Value,Otherfield as Key   <<--using if(wildmatch()) below

3) Load Data using the variable created in step 2

set vRoot = 'lib:/somedirectory/somesubdirectory/finalXmldirectory/';


FOR Each FileName in FileList('$(vRoot)'&'*.qvd')     <<-------i'e tested this with QVd files you can change it to xml

TRACE $(FileName);
let vcount = Index('$(FileName)','/',SubStringCount('$(FileName)','/'));
trace $(vcount);
LET vTableName = Mid('$(FileName)',Index('$(FileName)','/',SubStringCount('$(FileName)','/'))+1);
TRACE $(vTableName);

Tmp:
First 10 Load *
FROM $(FileName)(qvd)
;

//Create string for load statement with alias for the non standard field
for i = 1 to NoOfFields('Tmp')
if i = 1 Then
vfieldList = if(WildMatch(FieldName(i,'Tmp'),'ID','VALUE')=0,FieldName(i,'Tmp')&' as Key',FieldName(i,'Tmp') );
Else
vfieldList = vfieldList &','&if(WildMatch(FieldName(i,'Tmp'),'ID','VALUE')=0,FieldName(i,'Tmp')&' as Key',FieldName(i,'Tmp') );
End If
next i

Drop table Tmp;

Main:
Load $(vfieldList) From $(FileName)(qvd);

//You xml files will continue to auto-concatenate

NEXT FileName;

Cheers

V