Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

2 Replies
Contributor II

Re: Create mapping tables from lists within fields

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...

Not applicable

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;

Community Browser