Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Dates, columns to rows, Scripting help needed

Hello All,

I have a complex requirement, any suggestion is much appreciated.

What I have:

A file with Monthly values, e.g: For a Line of Business A, June Amount is xxx $

I have a Qlikview dashboard with Facts and Dimensions and this file has to be handled directly by Qlikview but rest of the tables come from a database

A single LOB can have multiple Entities

What I need:

I will have to split the Monthly amount into Weeks and then into days omitting the weekends

I will have to introduce the dates according to the month mentioned in the file e.g. for June 2016, i will have to introduce dates for weekdays.

I will have to introduce rows for each date and for each LOB and each Entity

Please refer to the attached excel and let me know if more information is needed.

Thanks,

Ga

12 Replies
puttemans
Valued Contributor

Re: Dates, columns to rows, Scripting help needed

Hi there,

I would first add the dates. If you create a separate calendar for 2016, with only the weekdays, then based upon the Year variable and the Month variable, you could create a unique link (e.g. june-16) that also shows in your calendar. With a left join, you'd only add the necessary lines per month for each separate line.

With the week-function, you would then get a view on the weeks per month. You'll get e.g.36 instead of 1 to x for every month. In your example, you start with a full week, but what do you want to happen when you only have e.g. 2 days in the first week. Through ranking and mapping, you could identify the lowest week number per month, or you could pre-code it into your calendar as well, and have it loaded in the previous step.

Then the last step is to calculate the monthly amount into daily. That can be done with the if statement. As you have all the info in one line. If (week = 1, amount*0.1/5, if (week = 2, amount*0.15/5, if(week = 3, amount*0.25/5, if(week = 4, amount*0.5/5, '0')))) This should give you per line a separate date, a week, a month an a correct amount. And from there you can go on.

Regards,

johan

Not applicable

Re: Dates, columns to rows, Scripting help needed

Thanks for your reply Johan.

I have a calendar in my data model , can it be used to include a date column?

And a small clarification on the amount calculation, it should be like, for week1 it is always 10% of the Total Amount divided by the number of days in that week. so if there are 2 days in that week, the amount should be divided by 2.

My calendar table, has a weekday flag, could it be used here? if so, please guide me to implement it.

All i need is a new column with dates so that i could connect to the calendar table in my data model and the calculated amount in rows from the single month amount column.

puttemans
Valued Contributor

Re: Dates, columns to rows, Scripting help needed

Please find attached the kind of calendar I'd use.

The first field should be similar to your table, so that the join will be made. You may need to script like this

left join

load

         month&year,

          date,

          week

FROM.....

WHERE len(week) = 1;

That way, you will not load the weekends.

puttemans
Valued Contributor

Re: Dates, columns to rows, Scripting help needed

for the number of days, then I think the easiest would be to already add the info. You can script it, but the effort may not weigh up against adding it to the calendar file, and thus importing the info directly.

The example should then be somewhat changed to the attched

Not applicable

Re: Dates, columns to rows, Scripting help needed

Hello Johan,

Can you please help me with a sample application?

Thanks.

sasikanth
Valued Contributor III

Re: Dates, columns to rows, Scripting help needed

hi,

Try below script

Table:

load* ,

MakeDate(Year(Date#(right(Year,2),'YY')),month(date#(Left(Month,3),'MMM')) ) as Date;

LOAD * Inline [

Code,Desc, Line of Business, Year, Month, Amount

1 ,A1 ,LOB1, FY16, June, 8845867.549

];

LET vVal=num(Peek('Date',0,'Table'));

For i=day(MonthStart('$(vVal)')) to day(MonthEnd('$(vVal)'))

     LET VNew=date($(vVal)+($(i)-1));

       PreFinal:

       Load *, date('$(VNew)') as New_Date,

            IF( Ceil(day('$(VNew)')/7)=1,(Amount*0.10)/5,

            IF(Ceil(day('$(VNew)')/7)=2, (Amount*0.15)/5,

            IF(Ceil(day('$(VNew)')/7)=3, (Amount*0.25)/5,

            IF(Ceil(day('$(VNew)')/7)=4, (Amount*0.50)/5)))) as disAmount,

            'W'& Ceil(day('$(VNew)')/7) as Week Resident Table where WeekDay('$(VNew)')<5 ;

NEXT i;

Drop Tables Table;

May be helpful

Thanks,

puttemans
Valued Contributor

Re: Dates, columns to rows, Scripting help needed

Hello Ganesh,

Please find attached a sample. I've taken your input, and created the calendar for June.Just let me know if you need an extra.

Kind regards,

Johan

Not applicable

Re: Dates, columns to rows, Scripting help needed

Thanks for your help Sasi. I will look into this and reply.

Not applicable

Re: Dates, columns to rows, Scripting help needed

Thanks for your time and efforts Johan. There is a small change to the input file and i have attached it here. Also the calendar which i already have is attached as well. Can you please give me your suggestions?

Thanks.

Community Browser