help needed in converting excel and getting the required output

[fashid]

I have an excel file which i have to load into qlikview and convert it .

The original excel and how the output should be are attached .

If anybody could please help me in doing this or showing me a direction. I am a newbie in qlikview

Regards,

Shaikh Nadeem

[arsal_90]

Sir what u want because your excel files show different perspective. Can u elaborate ur requirement

[#3]

Attached.

Note: This is not the best solution. Please look at the code. The way I did it you have to repeat the code for all 12 months if there were any. Now if there are different years thats a whole another animal.

I am sure you can use For each loop in the script to do the same irrespective of months or years. You can play around with it and tweak it per needs.

Thanks

AJ

[fashid]

My requirement is such that i have been given an excel file(Original excel) and i have been told to load into qilview and transform it such that the output resembles the file shown by (required output).

if the start date and end date fall in the same month but if the start date and end date differs in months or years

eg    NAME       START DATE   END DATE

            B             12-JAN-2014      02-FEB-2014

then the out put should come as below

          NAME       START DATE   END DATE

            B             12-JAN-2014   31-JAN-2014  

            B             01-FEB-2014  02-FEB-2014

HOPE I AM CLEAR LET ME KNOW IF ANY MORE INFO IS NEEDED

THANKS AND REGARDS

NADEEM SHAIKH

[tresesco · Accepted solution]

May be like attached sample.

Input:
Load * Inline [
Name, "Start Date", "End Date"
A, 01-Jan-14, 10-Jan-14
B, 12-Jan-14, 02-Feb-14
C, 01-Jan-14, 03-Mar-14

];

NoConcatenate
Output:
Load
Name,
[Start Date],
Date(If(AddMonths(MonthEnd([Start Date]), IterNo()-1)<[End Date], AddMonths(MonthEnd([Start Date]), IterNo()-1), [End Date]),'DD-MMM-YY') as [End Date]

Resident Input While AddMonths(MonthEnd([Start Date]), IterNo()-1)<=MonthEnd([End Date]);

Drop Table Input;

[#6]

tresesco's is the perfect solution.

[fashid]

Thanks A Million