data transformation

[nareshthavidishetty]

Hi all,

I stuck at some point as I got some excel data like fields empid,name,process,and dates I mean 1-jan,2-jan,3-jan

so on upto 31-jan when I load this kind of data it is giving me as result

LOAD [Emp. Id.],

     Name,

     Process,

    serialno,

     [Billable(Y/N)],

     [42552],

     [42553],

     [42554],

     [42555],

     [42556],

     [42557],

     [42558],

how can I solve as I'm looking for date column or month

[Anil_Babu_Samineni]

Can you post your excel

[arethaking]

nareshthavidishetty‌

attach the sample application.

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

Temp:

CrossTable(TempDate, Value,5)

LOAD * FROM ...source_file...;

Result:

LOAD

     *,

     Date(num#(TempDate),'D-MMM-YY') as Date,

     Month(num#(TempDate)) as Month

RESIDENT

     Temp;

DROP TABLE Temp;

DROP FIELD TempDate;

[nareshthavidishetty]

I tried ur solution and it worked ,thanks

as I got one more doubt that in the excel they are given at the end some more rows,like total strength,total Present etc so I need to delete those rows while loading itself (I'm looking for dynamic way)

[Anonymous]

these kind of scenarios already explained, please check this?

The Crosstable Load

[Gysbert_Wassenaar]

You can probably get rid of those with a where clause. I have no idea what to put in the where clause because I don't know which fields have which values on the total rows.

Result:

LOAD

     *,

     Date(num#(TempDate),'D-MMM-YY') as Date,

     Month(num#(TempDate)) as Month

RESIDENT

     Temp

WHERE

     [Emp. Id.] <> 'This is a total line';

[nareshthavidishetty]

thanks for ur response