Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

data transformation

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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;


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Anil_Babu_Samineni

Can you post your excel

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
arethaking
Creator II
Creator II

nareshthavidishetty

attach the sample application.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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;


talk is cheap, supply exceeds demand
nareshthavidishetty
Creator III
Creator III
Author

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
Not applicable

these kind of scenarios already explained, please check this?

The Crosstable Load

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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';


talk is cheap, supply exceeds demand
nareshthavidishetty
Creator III
Creator III
Author

thanks for ur response