Suggestions required

Anonymous — Mon, 25 Jul 2016 15:01:27 GMT

Hi All,

I want to have cross table but data is bifurcated into multiple columns as attached in the sample.

Means when the crosstable is done the Date part and the qty part can be derived accordingly but the linking of Date with qty is also broken.

Kindly suggest how to achieve.

What I am doing is this:

CrossTable(Type,Data,5)

LOAD [Part No],

Plant,

[Plant Short Name],

Supplier,

[Model Yr],

[Release Week-1 Date],

[Release Week-1 Qty],

[Release Week-2 Date],

[Release Week-2 Qty],

[Release Week-3 Date],

[Release Week-3 Qty],

[Release Week-4 Date],

[Release Week-4 Qty],

[Release Week-5 Date],

[Release Week-5 Qty],

[Release Week-6 Date],

[Release Week-6 Qty],

[Release Week-7 Date],

[Release Week-7 Qty],

[Release Week-8 Date],

[Release Week-8 Qty],

[Release Week-9 Date],

[Release Week-9 Qty],

[Release Week-10 Date],

[Release Week-10 Qty],

[Release Week-11 Date],

[Release Week-11 Qty],

[Release Week-12 Date],

[Release Week-12 Qty],

[Release Week-13 Date],

[Release Week-13 Qty],

[Release Week-14 Date],

[Release Week-14 Qty]

From Table;

Final:

LOAD [Part No],

Plant,

[Plant Short Name],

Supplier,

[Model Yr]

Data ,

if(SubField(Type,' ',3)='Date','Date_Type','Qty_Type') as Type,

if(SubField(Type,' ',3)='Date',Data,null()) as Release_Date,

if(SubField(Type,' ',3)='Qty',Data,null()) as Release_Qty

Resident A;

drop table A;

Re: Suggestions required

sunny_talwar — Mon, 25 Jul 2016 16:23:53 GMT

How about like this:

CrossTable(Type,Data,5)

LOAD *

FROM

[..\..\..\Downloads\Sample (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD KeepChar(Type, '0123456789') as Key,

[Part No],

Plant,

[Plant Short Name],

Supplier,

[Model Yr],

Date(Num#(Data)) as Release_Date

Resident A

Where SubField(Type,' ',3) = 'Date';

Left Join (Final)

LOAD KeepChar(Type, '0123456789') as Key,

[Part No],

Plant,

[Plant Short Name],

Supplier,

[Model Yr],

Data as Release_Qty

Resident A

Where SubField(Type,' ',3) = 'Qty';

DROP Table A;

topic Suggestions required in QlikView

Suggestions required

Re: Suggestions required