Transpose data

[#1]

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

[sunny_talwar]

You need to use CrossTable Load here: The Crosstable Load

[sunny_talwar · Accepted solution]

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:

[Anonymous]

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.

[#5]

Hi Rajesh

PFA

Regards

Chitra

[#6]

Hi,

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

It still shows as 42095,42096

[sunny_talwar]

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

[#8]

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

[#9]

Have updated as this

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

still its not working

[sunny_talwar]

Try this:

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