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:

      master_fileprefixPIN
      file1adj;jkg;hfd;12345;54321;47585;42156;41265;45785;
      file2pdm;hjd;iedj;11111;22222;44444;53485;;;
      file3hsdh;hfh;uej;47585;41235;;;;;

      (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:

      Part_prefixOrder_PINOther_Data
      11111pdmlalalala
      12345hfddadadada

       

      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:

      master_fileprefix
      file1adj
      file1jkg
      file1hfd
      master_filePIN
      file112345
      file154321
      file147585

      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

      // 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:

      Table_x:

      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:

      Untitled.png

      Thanks a Ton

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

          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:

               

               

               

              Table_Master_All_Data:
              SQL SELECT  master_file, pin_list, prefix_list

              FROM database_Table

               

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

               

              Table_Prefixes_Map:

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