Is it possible to load data from different files and create uniform field names?

Wed, 30 Dec 2015 15:41:31 GMT

Hello everyone,

I have data that is being loaded from 6 different QVD files. Each file relates to a specific machine used at my work. The field names should all be the same, however, the person that programmed the server in which the QVD files are created from did not name each field consistently. For example, lets say that one of the fields is called "Elec_Lock_Test". In 2 of the 6 files this may be the case, however, 1 of the files the heading is titled "Elect_Lock_Test" or "Elec_Lock_test". I realize the best solution is to have him fix the server so it is all consistent, but for my own knowledge is it possible to load the fields and force them to all be the same without having separate load statements for each file? Basically, I pull the data from the server using an OLE DB connection. Within each of these loads I rename the inconsistent fields so that when they store into the QVD, the correct field names are saved. Then I load the QVD's using "*" in the file name to load the data from all 6. The load script for pulling the data from the server is quite long. Ideally I would like to have the Load from the server be a "Load * from" type of script and then when I pull the data from each QVD using the "*" in the file name, I could manually rename each field in one load statement. I tried the no label load approach (@1 as Elec..., @2 as Some other name, @3 as ....) but this doesn't work because the file has label headings. Please see the attached script to see what I'm working with. Is there a way to simplify the code so that the load can have the following logic?

Machine 1:

Load *;

SQL Select *

From ....

Store Machine 1 into M1.qvd

Repeat above script for Machines 2-6 saving a qvd for each (M2, M3, M4, M5, M6)

[Master Table]:

Load

@1 as ID

@2 as StationName

@3 as ....

@4 as ....

From [Lib://QVD Files/M*.qvd}

(I realize the above code is not correct, I was just short handing the logic I'm trying to explain)

Thank you again for your time!

Re: Is it possible to load data from different files and create uniform field names?

Gysbert_Wassenaar — Wed, 30 Dec 2015 18:12:45 GMT

Perhaps with something like this:

FieldMap:

MAPPING LOAD * INLINE [

Old, New

@1, First Field

@2, Second Field

@3, Third Field

@4, Fourth Field

@5, Fifth Field

@6, Sixth Field

];

SET vConcatenate = ;

SUB ScanFolder(Root)

FOR each FileExtension in 'qvd'

FOR each FoundFile in filelist( Root & '\*.' & FileExtension)

Temp:

FIRST 1

LOAD * FROM [$(FoundFile)] (qvd);

FOR i =1 to NoOfFields('Temp')

LET vVar = 'vField' & $(i);

LET '$(vVar)' = '['&FieldName($(i),'Temp') & ']';

LET vFieldList = '$(vFieldList)' & vField$(i) & ' as @$(i), ' ;

LET vFieldList = Left('$(vFieldList)',Len('$(vFieldList)')-2);

DROP TABLE Temp;

ResultTable:

$(vConcatenate)

LOAD $(vFieldList), '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (qvd);

SET vFieldList=;

SET vConcatenate = Concatenate;

NEXT FoundFile

NEXT FileExtension

END SUB

CALL ScanFolder('C:\QlikData\QVD') ;

RENAME FIELDS USING FieldMap;

SET i=;

SET vVar=;

topic Re: Is it possible to load data from different files and create uniform field names? in App Development

Is it possible to load data from different files and create uniform field names?

Re: Is it possible to load data from different files and create uniform field names?