topic Re: Data Transformation Help in QlikView

Data Transformation Help

tamilarasu — Fri, 14 Aug 2015 07:23:57 GMT

All,

I have a attendance report and I want to format the data before loading it into Qlikview. I have tried and got stuck at one point.

Below is my data:

Expected output like:

Thank you.

Re: Data Transformation Help

qlikviewwizard — Fri, 14 Aug 2015 07:45:19 GMT

Good question.

Re: Data Transformation Help

MK_QSL — Fri, 14 Aug 2015 07:47:46 GMT

While working I can see that

1) You mentioned Date but there is no date in any excel sheet. So we have to either add date for each load or you have to provide date also.

2) there are some rows hidden in you excel file. Let us know what you want to do with that?

Re: Data Transformation Help

kuczynska — Fri, 14 Aug 2015 07:50:30 GMT

You have to start from loading your data as a crosstable, please see attached qvw.

You have to decide what you want to do with hidden rows (I removed them all) and think about re-formatting your date field in Excel. You can of course start using MakeDate() function to format it properly in QV.

Re: Data Transformation Help

qlikviewwizard — Fri, 14 Aug 2015 08:34:35 GMT

Hi Kuczynska, nice.

Re: Data Transformation Help

tamilarasu — Fri, 14 Aug 2015 09:00:30 GMT

Hi Manish,

Sorry,I didn't explain my requirement clearly.

1) I have to make a date based on the sheet name. Say for example, I have to create a date by 01- Jan -2015 (Based on the sheet name).

2) I have removed the hidden rows and attached the same.

Thank a lot.

Re: Data Transformation Help

MK_QSL — Fri, 14 Aug 2015 09:03:11 GMT

OK.. you can use the same suggested by Kuczynska. Use crosstable and concatenate all the sheet's data.

Re: Data Transformation Help

tamilarasu — Fri, 14 Aug 2015 09:04:22 GMT

Thank you. Let me try It.

Re: Data Transformation Help

tamilarasu — Mon, 17 Aug 2015 04:53:51 GMT

Hello All,

Sorry I am back again because I didn't get the desired result. I haven't used cross table before might be the reason. In my case, I have unwanted columns (May vary in each sheet. The above attached file is a sample) after the blank column. In Kuczynska' solution, the date field names are hard coded. While through the sheets, date can be change from 1 to 31 or 1 to 30 or 1 to 28. So I am bit confused as I haven't used cross table before. Could you please suggest an Idea.