2 Replies Latest reply: Jun 22, 2015 8:17 AM by Brian Moote RSS

    Create mapping tables from lists within fields

      I have a table that contains a master_file, other columns, and 2 columns which contain lists. It looks something like this:


      (always 6 semicolons for pins, whether 6 pins exist or not)


      I need to sort out the prefixes and pins in order to map them to individual parts found in another table similar to:



      The part table has millions of parts and I only want to load the ones that belong to a master file based on containing both a prefix and a pin that are found in the file table.

      My thought on how to do this is to break the file table into 2 tables like this:


      That way if someone selects a file, all of the associated pins and prefixes will be selected.


      My trouble is I am very new to Qlikview and the programming syntax, though I have some experience Java and C#.

      My pseudocode to split the table into 2 looks like:


      // SQL load the file table from the database

      // Create a table with columns "file" and "PIN"

      // For each file in the main table   
           // load that file's pins (this is a string)

           // while there is another pin in the string

                 // insert the "file" and "pin" into the new table


      // end    


      // Repeat the process to make a table to map the file to prefixe

      // Drop the original main table ???



      The lines in bold are the ones I am having the most trouble with. I have already been able to load the main table just fine.


      Can anyone help with how I can create an empty table?


      In Qlikview tables are made by:


      SQL select x from y where z


      but i need something like:

      Declare Table Table_x(File_name char(20), PIN char(20))

      with the ability to insert lines during the pin analysis.


      Also, how do I loop through the rows of a table and pull a single cell of data from that row to loop through?

      Lastly, will this data model even work? any suggestions? currently it looks like this:


      Thanks a Ton

        • Re: Create mapping tables from lists within fields
          Subharaman Venkita

          I have created a sample file


          kindly check the script editor

          I have commented a small bit of code... uncomment if you require the tables separately


          for creating a blank table you can use the inline table syntax which is present in the code.


          Hope this helps...

            • Re: Create mapping tables from lists within fields

              Thanks for the help,

              I can't open the attached file. It says that I would have to change my settings to view it, since I'm using personal edition, and that I wouldn't be able to see any of my own documents again if I choose to change those settings. Perhaps a .txt file with the code...? or just copy paste. Though I've solved it, I'd be interested in still seeing how you went about it, and perhaps there's something I could learn from it.


              Anyway, if anyone has this problem I found an awesome solution after a ton of searching. Qlikview has a built-in funtion called substring(), which made the task super simple. I used it like this:




              SQL SELECT  master_file, pin_list, prefix_list

              FROM database_Table


              NoConcatenate LOAD
              master_file, SubField(pin_list, ';') as PIN
              Resident Table_Master_All_Data;



              NoConcatenate LOAD
              master_file, SubField(prefix_list, ';') as prefix
              Resident Table_Master;