Data Transformation Help

[tamilarasu]

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.

[qlikviewwizard]

Good question.

[MK_QSL]

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?

[kuczynska · Accepted solution]

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.

[qlikviewwizard]

Hi Kuczynska, nice.

[tamilarasu]

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.

[MK_QSL]

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

[tamilarasu]

Thank you. Let me try It.

[tamilarasu]

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.