Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Deleting Column from concatenated fact using defined structure

sushil353
Honored Contributor II

Deleting Column from concatenated fact using defined structure

Hi All,

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

concatenations

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

>> maintenance

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

[

a,b,c,m

];

for i=1 to NoOfFields('structure') /*taking all the column names into a single column to operate row level operations*/

SourceColumn:

LOAD

FieldName($(i),'structure') as structure_Col

AutoGenerate(1);

NEXT i;

DROP Table structure;

/*This is where all the concatenation happen.. you can use for loop with load* statement to reduce the code*/

table1:

LOAD * Inline

[

a,b,c,d,e,f,g,h

1,1,1,1,1,1,1,1

];

Concatenate(table1)

LOAD * Inline

[

a,b,c,m,n

2,2,2,2,2

];

/*taking all the column names into a single column to operate row level operations*/

for i=1 to NoOfFields('table1')

TargetColumn:

LOAD

FieldName($(i),'table1') as structure_Col2

AutoGenerate(1);

NEXT i;

/*to flag which is required column and which is not*/

Left Join(TargetColumn)

LOAD 1 as Deleteflag,structure_Col as structure_Col2 Resident SourceColumn;

DROP Table SourceColumn;

/*list of all the undesired column*/

DeleteColumn:

LOAD structure_Col2 Resident TargetColumn

where Deleteflag <> 1;

DROP Table TargetColumn;

temp:

LOAD Concat(structure_Col2,',') as test

Resident DeleteColumn

Group by 1;

let vDeleteColName = Peek('test',0,'temp');

DROP Fields $(vDeleteColName) from table1;

DROP Table temp;

HTH

Sushil

Version history
Revision #:
1 of 1
Last update:
‎10-19-2016 12:12 AM
Updated by: