16 Replies Latest reply: Jun 5, 2015 3:06 AM by Kumar Pramod RSS

    Table Creation

    Kumar Pramod

      Hi all,

       

      I need to create a table with 2 columns using QLIKSENSE.

       

      My requirement is first column is hardcoded. i have attached sample excel file how it should look.

       

      For that hardcoded value we have dynamic data in database. That dynamic data should place in second column.

       

      anyone please help to prepare table as i needed.

       

      Thanks,

      Pramod

        • Re: Table Creation
          Gysbert Wassenaar

          I'm afraid I don't understand your problem at all. Please read this document: QlikCommunity Tip: How to get answers to your post

          • Re: Table Creation
            Jonathan Poole

            What i suggest is to give the 1st column a name. Instead of "DATA NEEDED" , type "MEASURE NAME" or something like that.

            Capture.PNG

             

            Then load these values from your spreadsheet like this

             

            DataTable:

            Load

                 "Measure Name"

            from  <FolderConnection> ;

             

            Then right you can load the related data from your database (as long as you have the same 'measure name' field in the database

             

            left join (DataTable)

            Load

                 "Database Measure Name Field" as "Measure Name",

                "Database Measure Value" as "Values"

            SQL  ........

            From <DatabaseConnection>;

             

            That WILL work but it also means you are bringing back ALL the values from the database and then filtering them in QLIK which would be perceived as inefficient.

             

            So i suggest passing the list of values into the SQL where clause like this:

             

             

            //Read in the 1st colum values from the spreadsheet

            Values:

            Load

                 "Measure Name"

            from  <FolderConnection> ;

             

            //create a variable that stores all values as a comma delimitted list   ...'ABC','DEF','GHI'

            DelimittedValueList:

            load

              chr(39) & concat(distinct [MeasureName], chr(39) & ',' & chr(39)) & chr(39) as ValueList

            resident Values;

             

            let vList=peek('ValueList',0,'DelimittedValueList');

             

             

            //Connect to the database

            LIB CONNECT TO 'DatabaseConnection';

             

            //Load in only the values that match from the database using ('ABC','DEF','GHI')

            LOAD `Salesman ID`,

                Salesman;

            SQL SELECT

                 "Measure Name from Database",

                 "Measure Value from Database"

            FROM <DatabaseConnection>

            where 'Measure Name from Database` in ($(vList))

            ;