Cross table in single table two times

[abhaysingh]

Hey guys,

i have this sample data with same fields i have, i want to convert it into rows to column twice, one for Month

PD1

PD10

PD11

PD12

PD2

PD3

PD4

PD5

PD6

PD7

PD8

PD9

and other is for

PLAN_QTY1

PLAN_QTY10

PLAN_QTY11

PLAN_QTY12

PLAN_QTY2

PLAN_QTY3

PLAN_QTY4

PLAN_QTY5

PLAN_QTY6

PLAN_QTY7

PLAN_QTY8

PLAN_QTY9

i m struggling in this.. records are multiplying..

[Anonymous]

Hi Abhay,

use cross table prefix and load data twice for each field(Plan and Month)

and Left Join using composite key(since your data doesn't have primary unique key),

hence duplicating of Records.

Regards

Praneetha

[jagan]

HI,

Try like this

Temp:

CrossTable(ValueType, Value, 2)

LOAD

Field1,

Field2, 

PD1,

PD10,

PD11,

PD12,

PD2,

PD3,

PD4,

PD5,

PD6,

PD7,

PD8,

PD9, PLAN_QTY1, PLAN_QTY7, PLAN_QTY8, PLAN_QTY9, PLAN_QTY10, PLAN_QTY11, PLAN_QTY12, PLAN_QTY2, PLAN_QTY3, PLAN_QTY4, PLAN_QTY5, PLAN_QTY6 FROm DataSoruce;

Data:

LOAD

Field1,

Field2,

If(ValueType LIKE 'PD*', 'PD', 'PLAN_QTY') AS DataType,

KeepChar(ValueType, '1234567890') AS DataNumber,

Value

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

[abhaysingh]

how to get the month for PD and PlanQty??

[jagan]

HI,

The field DataNumber is having the month for PD and PlanQty.  You can use flag DataType to filter PD and PLAN_QTY.

Regards,

Jagan.

[settu_periasamy]

Hi,

Check the Attachment.

[rajeshforqlikvi]

If possible then separate the excel file into two like keep the all records with these field PD1PD10PD11PD12PD2PD3PD4PD5PD6PD7PD8PD9

and second time keep the all records with these fields

PLAN_QTY1PLAN_QTY10PLAN_QTY11PLAN_QTY12PLAN_QTY2PLAN_QTY3PLAN_QTY4PLAN_QTY5PLAN_QTY6

PLAN_QTY7

PLAN_QTY8

PLAN_QTY9

now use separately cross table for both the excel files and join the table by using one primary key between the tables..

[settu_periasamy]

Hi AbhaySingh

Is this working as you expected?