Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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