topic Re: Transpose data in QlikView

Transpose data

Mon, 10 Aug 2015 10:34:26 GMT

Hi All,

I have teh date as given below in excel .

Bankid	4/1/2015	4/2/2015	4/3/2015	4/4/2015	4/5/2015	4/6/2015
1171556	85	135	0	0	37	33
1193366	547	467	0	0	0	309
1194820	408	446	0	132	0	388
1195227	495	641	0	0	0	0
1202500	465	452	110	0	0	172
1206780	484	345	87	0	0	0

But the data needs to be captures in 3 columns ( Bank ID , Date, Marcount)

for ex

Bank ID	Date	Mar count
1171556	4/1/2015	85
1171556	4/2/2015	135
1171556	4/3/2015	0
1171556	4/4/2015	0
1171556	4/5/2015	37
1171556	4/6/2015	33

Re: Transpose data

sunny_talwar — Mon, 10 Aug 2015 10:36:24 GMT

You need to use CrossTable Load here: The Crosstable Load

Re: Transpose data

sunny_talwar — Mon, 10 Aug 2015 10:44:59 GMT

Try the following Script

Table:

LOAD * Inline [

Bankid, 4/1/2015, 4/2/2015, 4/3/2015, 4/4/2015, 4/5/2015, 4/6/2015

1171556, 85, 135, 0, 0, 37, 33

1193366, 547, 467, 0, 0, 0, 309

1194820, 408, 446, 0, 132, 0, 388

1195227, 495, 641, 0, 0, 0, 0

1202500, 465, 452, 110, 0, 0, 172

1206780, 484, 345, 87, 0, 0, 0

];

CrossTable:

CrossTable (Date, Value)

LOAD *

Resident Table;

FinalTable:

NoConcatenate

LOAD Bankid,

Date#(Date, 'MM/DD/YYYY') as Date,

Value

Resident CrossTable;

DROP Tables Table, CrossTable;

Output in Table Box Object:

Re: Transpose data

Anonymous — Mon, 10 Aug 2015 10:45:02 GMT

Hi,

Please follow this steps to achieve your desired output:

Step-1:Go to Edit Script and select the data source (Excel, CSV ...)

Step-2: Click on next button twice and after that click on CrossTable.

Above, you can select Qualifier fields, Name for Attribute Filed and Data Values. You can see the color combination with these related filed also. Press OK. Now Data is converted to Straight Table.

Now, Click on Finish.

Re: Transpose data

Mon, 10 Aug 2015 10:53:13 GMT

Hi Rajesh

PFA

Regards

Chitra

Re: Transpose data

Mon, 10 Aug 2015 11:15:02 GMT

Hi,

The only issue is when i upload from excel the date field is not coming correctly.

It still shows as 42095,42096

Re: Transpose data

sunny_talwar — Mon, 10 Aug 2015 11:16:55 GMT

Convert the number to Date using the Date function in a resident load

Re: Transpose data

Mon, 10 Aug 2015 12:12:19 GMT

Hi,

Following is the code.

tmp:

CrossTable (Date1, Marcount)

LOAD Bankid,

[42095],

[42096],

[42097],

[42098],

[42099],

[42100],

[42101],

[42102],

[42103],

[42104],

[42105],

[42106],

[42107],

[42108],

[42109],

[42110],

[42111],

[42112],

[42113],

[42114],

[42115],

[42116],

[42117],

[42118],

[42119],

[42120],

[42121],

[42122],

[42123],

[42124],

[42125],

[42126],

[42127],

[42128],

[42129],

[42130],

[42131],

[42132],

[42133],

[42134],

[42135],

[42136],

[42137],

[42138],

[42139],

[42140],

[42141],

[42142],

[42143],

[42144],

[42145],

[42146],

[42147],

[42148],

[42149],

[42150],

[42151],

[42152],

[42153],

[42154],

[42155],

[42156],

[42157],

[42158],

[42159],

[42160],

[42161],

[42162],

[42163],

[42164],

[42165],

[42166],

[42167],

[42168],

[42169],

[42170],

[42171],

[42172],

[42173],

[42174],

[42175],

[42176],

[42177],

[42178],

[42179],

[42180],

[42181],

[42182],

[42183],

[42184],

[42185],

[42186],

[42187],

[42188],

[42189],

[42190],

[42191],

[42192],

[42193],

[42194],

[42195],

[42196],

[42197],

[42198],

[42199],

[42200],

[42201],

[42202],

[42203],

[42204],

[42205],

[42206],

[42207],

[42208],

[42209],

[42210],

[42211],

[42212],

[42213],

[42214],

[42215],

[42216]

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

Load Bankid,

Date#(Date1, 'MM/DD/YYYY') as Processdate,

Marcount

Resident tmp;

Drop Table tmp;

Inspite oif it it still shows in number format

Re: Transpose data

Mon, 10 Aug 2015 12:14:22 GMT

Have updated as this

Date(Date#(Date1, 'MM/DD/YYYY'),'YYYY-MM-DD') as Processdate,

still its not working

Re: Transpose data

sunny_talwar — Mon, 10 Aug 2015 12:31:00 GMT

Try this:

Date(Num#(PurgeChar(Date1, '[]'), '##'),'YYYY-MM-DD') as Processdate,

Re: Transpose data

Mon, 10 Aug 2015 12:42:26 GMT

It works Thanks

Re: Transpose data

sunny_talwar — Mon, 10 Aug 2015 14:10:12 GMT

Awesome

I am glad it worked for you.

Best,

Sunny