topic Re: data transformation in QlikView

data transformation

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: data transformation

Anil_Babu_Samineni — Fri, 02 Sep 2016 07:40:41 GMT

Can you post your excel

Re: data transformation

arethaking — Fri, 02 Sep 2016 07:46:01 GMT

nareshthavidishetty‌

attach the sample application.

Re: data transformation

Gysbert_Wassenaar — Fri, 02 Sep 2016 07:50:27 GMT

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;

Re: data transformation

nareshthavidishetty — Fri, 02 Sep 2016 08:14:18 GMT

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)

Re: data transformation

Anonymous — Fri, 02 Sep 2016 08:23:10 GMT

these kind of scenarios already explained, please check this?

The Crosstable Load

Re: data transformation

Gysbert_Wassenaar — Fri, 02 Sep 2016 08:24:17 GMT

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

Re: data transformation

nareshthavidishetty — Fri, 02 Sep 2016 08:27:37 GMT

thanks for ur response