topic Re: Cross table in single table two times in QlikView

Cross table in single table two times

abhaysingh — Wed, 27 Jan 2016 08:27:22 GMT

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

Re: Cross table in single table two times

Anonymous — Wed, 27 Jan 2016 08:55:04 GMT

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

Re: Cross table in single table two times

jagan — Wed, 27 Jan 2016 09:14:29 GMT

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.

Re: Cross table in single table two times

abhaysingh — Wed, 27 Jan 2016 13:28:11 GMT

how to get the month for PD and PlanQty??

Re: Cross table in single table two times

jagan — Thu, 28 Jan 2016 01:44:07 GMT

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.

Re: Cross table in single table two times

settu_periasamy — Thu, 28 Jan 2016 03:36:55 GMT

Hi,

Check the Attachment.

Re: Cross table in single table two times

rajeshforqlikvi — Thu, 28 Jan 2016 05:00:08 GMT

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

Re: Cross table in single table two times

settu_periasamy — Fri, 29 Jan 2016 02:31:08 GMT

Hi AbhaySingh

Is this working as you expected?