1 Reply Latest reply: Oct 7, 2016 10:24 AM by Jean-Pierre Noble RSS

    Loading a QVD without the embedded labels

    Jean-Pierre Noble

      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?

        • Re: Loading a QVD without the embedded labels
          Jean-Pierre Noble

          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