Suggestions required

[Anonymous]

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;

[sunny_talwar · Accepted solution]

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;