Loading a QVD without the embedded labels

[Anonymous]

Hi Qlik Experts

I'm trying to use Qlik as a tool to do analysis of our database set up and as part of that process I'm looking to pull in data from each of our tables and cycle through a series of checks and store the results against each of the Field names of the table.

I can get the list of fields in the table using the below code:

QvdFieldHeader:

LOAD FieldName

FROM "O:\Data\TABLE1.qvd" (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

So the loop that I use and that works if I import Excel data without labels is:

/*This Variable will act as the Benchmark for the test*/

SET vColumnNo = '1';

/*This Variable will be used to limit the loop cycle*/

LET vCalculatingColumn = '@'&$(vColumnNo);

/* Create first row of the table before starting the Loop. */

FieldTemp:

LOAD

Distinct $(vCalculatingColumn) as FieldValues,

len($(vCalculatingColumn)) as FieldLength

Resident TempData;

/*  Joining the Field Name  */

Left Join(FieldTemp)

FieldName:

LOAD

FieldName as FieldName

Resident QvdFieldHeader where RowNo()=$(vColumnNo);

/* Create the table in which the data will be stored */

NoConcatenate

FieldData:

LOAD *

Resident FieldTemp;

DROP Table FieldTemp;

/* Start loop script that will continue until condition is met. */

Do until vColumnNo=NoOfFields('TempData')

LET vColumnNo = $(vColumnNo)+1;

LET vCalculatingColumn = '@'&$(vColumnNo);

NoConcatenate

FieldTemp:

LOAD

Distinct $(vCalculatingColumn) as FieldValues,

len($(vCalculatingColumn)) as FieldLength

Resident TempData;

/*  Joining the Field Name  */

Left Join(FieldTemp)

FieldName:

LOAD

FieldName as FieldName

Resident QvdFieldHeader where RowNo()=$(vColumnNo);

Concatenate (FieldData)

LOAD *

Resident FieldTemp;

Drop Tables FieldTemp;

loop

Is there a way for me to set this to cycle through the QVD columns or do I need to store the excels of data which is much slower?

[Anonymous · Accepted solution]

OK so looks like with a bit of playing around and reading some other threads I have found the FieldName() Fumction that has solved my query and allowed me to create this loop below that is much more efficient for what I need.

TABLE1:

LOAD * FROM

SELECT

*

FROM "O:\Data\TABLE1.qvd";

//Setting the Column number to start the process

SET vColumnNo = '0';

/* Start loop script that will continue until condition is met. */

Do until vColumnNo=NoOfFields('TABLE1')

/*Set variable to increment*/

LET vColumnNo = $(vColumnNo)+1;

/*Set variable to pick column*/

LET vCalculatingColumn = FieldName($(vColumnNo),'$(vTable)');

/*Set variable to return the current Fieldname being used*/

LET vCalculatingColumnName = CHR(39)&FieldName($(vColumnNo),'$(vTable)')&CHR(39);

/*Action to carry out to pull the Values and field length against the FieldName*/

FieldData:

LOAD

Distinct

$(vCalculatingColumnName) as FieldName,

[$(vCalculatingColumn)] as FieldValues,

len([$(vCalculatingColumn)]) as FieldLength

Resident TABLE1;

loop