Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a function in Excel that i would like to implement in qlikview. In my sheet there's a table as presented below
And here are the functions:
I dont have an idea how to calculate expression for active column. I tried to use Above but it didn't really work well :
as you can see, first row is correct, second also, but in third the value in active should be equal to 1, instead it shows 0 because above function sees 9-9=0 equation and don't remember about this '1' which left from previous row.
Any idea how to fix this ?
Hi,
another one creating a calendar table with your date_new and date_finished fields to generate combined week and date fields for both events.
The count of new and finished events per week can then be done using this week and canonical date field:
tabData:
LOAD RecNo() as ID,
Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_new,
Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_finished
AutoGenerate 100;
tabCalendar:
CrossTable (FieldName, Date)
LOAD * Resident tabData;
Left Join (tabCalendar)
LOAD Distinct
Date,
Dual('W'&Num(Week(Date), '00'), WeekStart(Date)) as Week
Resident tabCalendar;
Left Join (tabCalendar)
LOAD Distinct
FieldName,
SubField(FieldName, '_', 2) as DateType
Resident tabCalendar;
DROP Field FieldName;
hope this helps
regards
Marco
Try this script which gives the desired result.
LOAD *,If(IsNull(Peek('active')),new-finished,Peek('active')+new-finished) as active;
LOAD * Inline [
Week,new,finished
W06,6,3
W07,9,3
W08,4,3
W09,9,2
W10,13,6
W11,11,14
];
Hi,
one solution with calculations in the script:
LOAD *,
Alt(Peek(active),0)+(new-finished) as active
INLINE [
week, new, finished
W06, 6, 3
W07, 9, 3
W08, 4, 3
W09, 9, 2
W10, 13, 6
W11, 11, 14
];
hope this helps
regards
Marco
Hi,
another one creating a calendar table with your date_new and date_finished fields to generate combined week and date fields for both events.
The count of new and finished events per week can then be done using this week and canonical date field:
tabData:
LOAD RecNo() as ID,
Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_new,
Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_finished
AutoGenerate 100;
tabCalendar:
CrossTable (FieldName, Date)
LOAD * Resident tabData;
Left Join (tabCalendar)
LOAD Distinct
Date,
Dual('W'&Num(Week(Date), '00'), WeekStart(Date)) as Week
Resident tabCalendar;
Left Join (tabCalendar)
LOAD Distinct
FieldName,
SubField(FieldName, '_', 2) as DateType
Resident tabCalendar;
DROP Field FieldName;
hope this helps
regards
Marco