topic Problem with expression in QlikView

Problem with expression

Sun, 07 Sep 2014 11:19:28 GMT

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 ?

Re: Problem with expression

nagaiank — Sun, 07 Sep 2014 11:33:35 GMT

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

];

Re: Problem with expression

MarcoWedel — Sun, 07 Sep 2014 11:39:33 GMT

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

Re: Problem with expression

MarcoWedel — Sun, 07 Sep 2014 12:25:31 GMT

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