Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist
Specialist

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
Author

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
Specialist
Specialist

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
Specialist
Specialist

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
Author

Hello Johan,

Can you please help me with a sample application?

Thanks.

sasikanth
Master
Master

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
Specialist
Specialist

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
Author

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

Not applicable
Author

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.