Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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;