Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jn745402
Not applicable

Loading a QVD without the embedded labels

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?

1 Solution

Accepted Solutions
jn745402
Not applicable

Re: Loading a QVD without the embedded labels

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

1 Reply
jn745402
Not applicable

Re: Loading a QVD without the embedded labels

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