topic Re: Dates, columns to rows, Scripting help needed in QlikView

Dates, columns to rows, Scripting help needed

Fri, 16 Sep 2016 11:50:47 GMT

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,

Re: Dates, columns to rows, Scripting help needed

puttemans — Fri, 16 Sep 2016 13:16:44 GMT

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

Re: Dates, columns to rows, Scripting help needed

Fri, 16 Sep 2016 13:42:30 GMT

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.

Re: Dates, columns to rows, Scripting help needed

puttemans — Fri, 16 Sep 2016 13:56:26 GMT

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.

Re: Dates, columns to rows, Scripting help needed

puttemans — Fri, 16 Sep 2016 14:00:19 GMT

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

Re: Dates, columns to rows, Scripting help needed

Tue, 20 Sep 2016 14:13:52 GMT

Hello Johan,

Can you please help me with a sample application?

Thanks.

Re: Dates, columns to rows, Scripting help needed

sasikanth — Tue, 20 Sep 2016 15:17:27 GMT

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,

Re: Dates, columns to rows, Scripting help needed

puttemans — Wed, 21 Sep 2016 07:20:16 GMT

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

Re: Dates, columns to rows, Scripting help needed

Wed, 21 Sep 2016 14:21:04 GMT

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

Re: Dates, columns to rows, Scripting help needed

Wed, 21 Sep 2016 15:47:05 GMT

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.

Re: Dates, columns to rows, Scripting help needed

puttemans — Fri, 23 Sep 2016 08:18:32 GMT

Hello Ga,

Please find attached the example based on your newest documents. It needed a tweak because of the crosstable you want to load.

I've also added one column extra in the input-file for the number of days (NRDAYOFWEEK). If you're not able to add this line in your basic input, you may need to calculate it first within the application.

Kind regards,

Johan

Re: Dates, columns to rows, Scripting help needed

Fri, 23 Sep 2016 14:13:14 GMT

Thanks a lot for the help.

Re: Dates, columns to rows, Scripting help needed

Fri, 23 Sep 2016 14:13:56 GMT

Let me try to calculate the Number of days in a week at Script level. I will reach out to you, if i fail at it.Thanks again