Create mapping tables from lists within fields

[#1]

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

master_file	prefix	PIN
file1	adj;jkg;hfd;	12345;54321;47585;42156;41265;45785;
file2	pdm;hjd;iedj;	11111;22222;44444;53485;;;
file3	hsdh;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_prefix	Order_PIN	Other_Data
11111	pdm	lalalala
12345	hfd	dadadada

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_file	prefix
file1	adj
file1	jkg
file1	hfd

master_file	PIN
file1	12345
file1	54321
file1	47585

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:

Thanks a Ton

[svenkita]

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

[#3]

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;