Documents for QlikView related information.
This document is for those who are having xl data source in particular and for those who are used to create there fact table using multiple
In one of my requirement i have to do the same.. i have multiple fact tables (different granularity) and need to concatenate all..
But the problem was.. ... i have 20 odd tables and all having different columns/measures... and after concatenating all i have to take some of the column from the final table..
So. the main problem was :
>> The code was lengthy due to 20 table with all the column name mentioned.
>> chances of manual error.. missing out columns
to tackle this situation below is the general script that you can use in your code.. if required
Hope the script will help or give some idea to deal similar situation.
structure: /*this is the structure that i need in the final output of my fact table*/
LOAD * Inline
for i=1 to NoOfFields('structure') /*taking all the column names into a single column to operate row level operations*/
FieldName($(i),'structure') as structure_Col
DROP Table structure;
/*This is where all the concatenation happen.. you can use for loop with load* statement to reduce the code*/
/*taking all the column names into a single column to operate row level operations*/
for i=1 to NoOfFields('table1')
FieldName($(i),'table1') as structure_Col2
/*to flag which is required column and which is not*/
LOAD 1 as Deleteflag,structure_Col as structure_Col2 Resident SourceColumn;
DROP Table SourceColumn;
/*list of all the undesired column*/
LOAD structure_Col2 Resident TargetColumn
where Deleteflag <> 1;
DROP Table TargetColumn;
LOAD Concat(structure_Col2,',') as test
Group by 1;
let vDeleteColName = Peek('test',0,'temp');
DROP Fields $(vDeleteColName) from table1;
DROP Table temp;