Load Specific Columns

[anushree1]

Hi,

I have a requirement wherein I would like to load data for those cols that has data

For Eg As per the attachment I have 5 Divisons

Division A has Data in Col 2 and 4,

Division B has data for Col 1&3

Division C has Data for 5 & 6

Division D has Data for 7 & 10

Division E had Data for 8 & 9

I want to load the cols for each Division that has data and rename the col as 5 and 6

so Division A I should load only Col 2 and 4,For Division B I should load only Col 1 and 3, etc.. for all Divisions and then concatenate them as a single set  with Cols Div,5, 6 as below dynamically:

Div	5	6
A	10	20
B	30	10
C	50	10
D	50	20
E	40	30

Please let me how this could be accomplished using the attached data

[juan_patrick]

Hi Anushree!

Try with this:

Table:

load

   div,

   C as 5,

   E as 6

from

   (excel)

where

   div = 'A';

concatenate (table)

load

   div,

   B as 5,

   D as 6

from

   (excel)

where

   div = 'B';

and so you concatenate as you wish.

¡Good Luck!

[ahaahaaha · Accepted solution]

Hi,

May be like this (look attached file)

LOAD Div,

Pick(Match(Div, 'A', 'B', 'C', 'D', 'E'), [2], [1], [5], [8], [7]) as 5,

Pick(Match(Div, 'A', 'B', 'C', 'D', 'E'), [4], [3], [6], [9], [10]) as 6

FROM [Path your table...]

Regards,

Andrey

[vinieme12]

try as below

temp:

CrossTable(COL, Data)

LOAD Div,

    [1],

    [2],

    [3],

    [4],

    [5],

    [6],

    [7],

    [8],

    [9],

    [10]

FROM

(ooxml, embedded labels, table is Sheet1);

temp2:

load *,AutoNumber(COL,Div)+4 as newCOL Resident temp;

drop table temp;

FACT:

LOAD Div,Data as 5

Resident temp2

where newCOL = 5;

left join(FACT)

LOAD Div,Data as 6

Resident temp2

where newCOL = 6;

Drop table temp2;

[anushree1]

Is adding 4 to autonumber essential here .

[vinieme12]

not really necessary

you could also use a Mapping table to RENAME  the field later on like below

newname:

MAPPING LOAD * inline [

oldname,newname

1,5

2,6

];

below is much more dynamic script

//// Script start///

temp:

CrossTable(COL, Data)

LOAD Div,

    [1],

    [2],

    [3],

    [4],

    [5],

    [6],

    [7],

    [8],

    [9],

    [10]

FROM

(ooxml, embedded labels, table is Sheet1);

temp2:

load *,AutoNumber(COL,Div) as newCOL Resident temp;

drop table temp;

MaxMin:

LOAD Max(newCOL) as MaxNum,

Min(newCOL) as MinNum

Resident temp2;

let vStart = peek('MinNum',0,'MaxMin');

let vEnd =  peek('MaxNum',0,'MaxMin');

for i = $(vStart) to $(vEnd)

  if $(i)=1 THEN

  FACT:

  LOAD Div,Data as $(i)

  Resident temp2

  where newCOL = $(i);

  ELSE

  left join(FACT)

  LOAD Div,Data as $(i)

  Resident temp2

  where newCOL = $(i);

  End IF

Next i

Drop table temp2;

newfieldname:

Mapping load * inline [

oldfield,newfield

1,5

2,6

];

Rename field using newfieldname;

//// Script END///

[Kushal_Chawda]

Data:

CrossTable(Field, FieldValue)

LOAD Div,

     [1],

     [2],

     [3],

     [4],

     [5],

     [6],

     [7],

     [8],

     [9],

     [10]

FROM

[Load Specific Cols.xlsx]

(ooxml, embedded labels, table is Sheet1);

New:

LOAD Div,

     SubField(Values,'|',1) as 5,

     SubField(Values,'|',1) as 6;

LOAD

     Div,

    concat(FieldValue,'|',trim(Field)) as Values

Resident Data

where len(trim(FieldValue))>0

Group by Div;

DROP Table Data;