topic Re: Urgent Help in QlikView

Urgent Help

soha1902 — Thu, 16 Jul 2015 08:08:43 GMT

Hi All,

I have following data:

Date Time Value

12 june 2015 10:00 25

12 june 2015 9:00 30

12 june 2015 8:00 35

11 june 2015 11:00 20

11june 2015 10:00 35

My Requirement is

Date Time Value NewCol

12 june 2015 10:00 25 5

12 june 2015 9:00 30 5

12 june 2015 8:00 35 0

11 june 2015 11:00 20 10

11june 2015 10:00 35 0

Logic: for smallest time value would be 0 for every similar date and other value will be Value - Previous(Value). I want to implement this in Script level only. I am using personal edition so plz share the script/code.

Thanks

Re: Urgent Help

soha1902 — Thu, 16 Jul 2015 08:12:07 GMT

New col value is 15 in place of 10.

Re: Urgent Help

MK_QSL — Thu, 16 Jul 2015 08:13:50 GMT

Unable to understand your logic ! Please describe in little more depth.

Re: Urgent Help

MK_QSL — Thu, 16 Jul 2015 08:23:59 GMT

Data:
Load
  Date(Date#(Date,'DD MMMM YYYY')) as Date,
  Time(Time#(Time,'h:mm'),'hh:mm') as Time,
  Value
Inline
[
  Date,         Time,   Value
  12 june 2015, 10:00,  25
  12 june 2015, 9:00,   30
  12 june 2015, 8:00,   35
  11 june 2015, 11:00,  20
  11 june 2015, 10:00,  35
];


Left Join (Data)
Load 
  Date,
  Time(Min(Time),'hh:mm') as MinTime
Resident Data
Group By Date;


Left Join (Data)
Load 
  Date, 
  Time,  
  1 as Flag
Resident Data Where Time = MinTime;


NoConcatenate
Final:
Load 
  Date,
  Time,
  Value,
  If(Flag = 1, 0, FABS(Value - Previous(Value))) as NewCol
Resident Data
Order By Date, Time;


Drop Table Data;

Re: Urgent Help

swuehl — Thu, 16 Jul 2015 08:31:17 GMT

Data:  
Load  
  Date,  
  Time,
  Value  
Inline  
[  
  Date,         Time,   Value  
  12 june 2015, 10:00,  25  
  12 june 2015, 9:00,   30  
  12 june 2015, 8:00,   35  
  11 june 2015, 11:00,  20  
  11 june 2015, 10:00,  35  
];  

Result:
LOAD Date, Time, Value, 
  if(peek(Date) = Date, Rangesum(Peek(Value),-Value),0) as NewVal
RESIDENT Data
ORDER BY Date, Time asc;


DROP TABLE Data;

Re: Urgent Help

tresB — Thu, 16 Jul 2015 08:36:48 GMT

Input:
Load
Date#(Date, 'DD MMMM YYYY') as Date,
Time#(Time, 'hh:mm') as Time,
Value
Inline [
Date,               Time ,              Value

12 june 2015,     10:00 ,              25

12 june 2015,     9:00 ,                 30

12 june 2015,     8:00 ,                  35

11 june 2015 ,    11:00   ,            20

11 june 2015 ,        10:00   ,            35

];
Output:
Load
Date,
Time,
If(RowNo()=1 or Date<>Previous(Date), 0, RangeSum(Previous(Value),-Value)) as NewValue,
Value
Resident Input Order By Date, Time;

Drop Table Input;