topic Re: Load Specific Columns in QlikView

Load Specific Columns

anushree1 — Tue, 04 Jul 2017 12:43:32 GMT

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

Re: Load Specific Columns

juan_patrick — Tue, 04 Jul 2017 12:55:06 GMT

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!

Re: Load Specific Columns

ahaahaaha — Tue, 04 Jul 2017 13:04:23 GMT

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

Re: Load Specific Columns

vinieme12 — Tue, 04 Jul 2017 13:37:57 GMT

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;

Re: Load Specific Columns

anushree1 — Tue, 04 Jul 2017 14:25:07 GMT

Is adding 4 to autonumber essential here .

Re: Load Specific Columns

vinieme12 — Thu, 06 Jul 2017 07:25:43 GMT

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///

Re: Load Specific Columns

Kushal_Chawda — Thu, 06 Jul 2017 09:21:52 GMT

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;