Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gupta_n8
Valued Contributor II

Suggestions required

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:

A:

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;

1 Solution

Accepted Solutions

Re: Suggestions required

How about like this:

A:

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;



View solution in original post

1 Reply

Re: Suggestions required

How about like this:

A:

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;



View solution in original post