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