Discussion Board for collaboration on QlikView Scripting.
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.
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.
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.
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
WHERE len(week) = 1;
That way, you will not load the weekends.
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
Try below script
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
For i=day(MonthStart('$(vVal)')) to day(MonthEnd('$(vVal)'))
Load *, date('$(VNew)') as New_Date,
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 ;
Drop Tables Table;
May be helpful
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.
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?