topic Re: Calculation possible in Scripting? in QlikView

Calculation possible in Scripting?

Fri, 24 Oct 2014 11:38:00 GMT

Hi All,

Lets say i have following columns

Date1 Date2 period Product Value

Now i want to write in script so that it will find me the difference of Value of two different dates.

Lets say

Date1 Date2 period Product Value

1/8/2014 1/8/2014 02 A 10

1/8/2014 20/8/2014 03 A 30

1/8/2014 25/08/2014 02 A 15

20/8/2014 20/8/2014 02 A 40

20/8/2014 17/9/2014 01 B 20

so now script sould find out if the two dates are equal

then (Value of (thatday+19 days) - (value on that day )

To be more clear when my script finds 1/8/2014 = 1/8/2014 then the output should be

( value on 20/08/2014) - ( value on 01/08/2014)

so O/P is 40 - 10 = 30... This will apply for other dates as well. Please help

Br,

Avi

Re: Calculation possible in Scripting?

Fri, 24 Oct 2014 11:54:52 GMT

Hi,

you can use the below.

if(Date1=Date2,sum(Value)-sum({<Date2={'$(=$(Date2-19))'}>}Value)

Re: Calculation possible in Scripting?

Fri, 24 Oct 2014 12:05:16 GMT

HI,

Can you tell me why you have used Sum function and also can you tell me the logic which you have given can be written in the script ? I want to implement in Script not in the front end

Re: Calculation possible in Scripting?

Peter_Cammaert — Fri, 24 Oct 2014 12:05:39 GMT

Just a question about the intended logic:

The value on (thatday + 19 days), should it be the value in the record with Date1 = Date 2?

As an example, imagine I have a record for product A where Date 1 = 13/7/2014 and Date 2 = 13/7/2014. Now there are three possibilities for value of (thatday + 19 days)

An easy solution could be with a mapping table.

Re: Calculation possible in Scripting?

Fri, 24 Oct 2014 12:16:08 GMT

HI Peter,

May be i might have put in wrong way.

Thatday in my sentence means the Date1 where Date1 = date2.

So i mean Thatday is Date1.

To be more clear when my script finds 1/8/2014 = 1/8/2014 then the output should be

( value of when Date1 +19 = Date2 ) - ( value on 01/08/2014 = 1/8/2014 ).

Date1 +19 is 20/08/2014. There will be only one occurence where 20/08/2014= 20/08/2014(Date1 = Date2)

The value on 20/08/2014 is 40 and The value on 01/08/2014 is 10 hence O/P is 40 - 10 = 30.

Am i clear ?

Please let me know if anything is confusing

Re: Calculation possible in Scripting?

Peter_Cammaert — Fri, 24 Oct 2014 12:26:52 GMT

I do understand that logic. What isn't clear to me is what happens if (thatday + 19 days) results in multiple possible values.

Please revisit my example. Imagine I add a record at the beginning of your table like the following:

Date 1 Date 2 period Product Value

13/7/2014 13/7/2014 02 A 5

Clearly Date 1 = Date 2, so your expression applies. But Thatday+19 results in 3 records for the same product with different values. What do we do? Sum all values (value = 55)? Take the one where Date 1 = Date 2 (value = 10)

Re: Calculation possible in Scripting?

Fri, 24 Oct 2014 12:40:49 GMT

HI Peter,

There will be only one occurence where Date1 = Date2 for a specific date. For a combination of Date1, Date2, period, Product

So i wont be having data like Date1 = Date2 for 3 times for a specific date for a combination.

So 13/7/2014 = 13/7/2014 occurence will be only once for the combination of

Date 1 Date 2 period Product

13/7/2014 13/7/2014 02 A

There can be another line where

Date 1 Date 2 period Product Value

13/7/2014 13/7/2014 02 B 50

Then this record should search for

Date 1 Date 2 period Product Value

1/08/2014 1/08/2014 02 B 100

And give output of 100- 50 = 50

It shouldnot search for the record of

Date 1 Date 2 period Product Value

1/08/2014 1/08/2014 02 A 100

Because Product is A. The above record should be matched with

Date 1 Date 2 period Product Value

13/07/2014 13/07/2014 02 A 100

Hope this has clarrified the doubt. If you still have any doubt please do let me know

Re: Re: Calculation possible in Scripting?

Peter_Cammaert — Fri, 24 Oct 2014 13:43:09 GMT

All clear.

How about adding something like this:

MapDate2Value19DaysOn:

MAPPING LOAD Date1 &'|' & period & '|' & Product as A, Value AS B

RESIDENT YourOriginalFactsTable

WHERE Date1 = Date2;

NewFactsTable:

LOAD Date1, Date2, period, Product,

IF (Date1 <> Date2, Value,

applymap('MapDate2Value19DaysOn', date(Date1 + 19) & '|' & period & '|' & Product, 0) - Value) AS Value

RESIDENT YourOriginalFactsTable;

Best,

Peter

Re: Calculation possible in Scripting?

Mon, 27 Oct 2014 07:03:50 GMT

HI Peter,

ThankYou I will try on the suggested and will get back to you

BR,

Re: Calculation possible in Scripting?

Tue, 28 Oct 2014 06:38:46 GMT

HI Peter,

It is working fine but

i am using one more logic where it is failing.

MapDate2Value:

MAPPING LOAD Date1 &'|' Date2 &'|'& period & '|' & Product as C, Value AS D

RESIDENT YourOriginalFactsTable;

NewFactsTable:

LOAD Date1, Date2, period, Product,

IF (Date1 <> Date2, Value,

applymap('MapDate2Value', Date1 &'|' date(Date1 + 7) & '|' & period & '|' & Product, 0) - Value) AS Value

RESIDENT YourOriginalFactsTable;

The above logic is for :

My Data

Date1 Date2 period Product Value

1/8/2014 1/8/2014 02 A 10

1/8/2014 20/8/2014 03 A 30

1/8/2014 25/08/2014 02 A 15

Logic is maintained so that it should give 30-10 = 20 , 15-10 = 5

Am i doing some wrong or what i am not understanding why it is not matching the records . The output I am getting is 0- Value(Because it is failing to match the recors)

IS there any limitation for Applymap?

Re: Calculation possible in Scripting?

Peter_Cammaert — Tue, 28 Oct 2014 11:01:29 GMT

Two thoughts:

In the NewFactsTable LOAD, you're missing an ampersand in the second parameter expression, between '|' and date()
If I read your new script correctly, you're trying to get hold of values in records that have Date2 = Date1 + 7 days, right? For the example data, this will fail because there is no record where Date1 = 1/8/2014 and Date2 = 8/8/2014.

You can obtain the required result if you reverse the logic for records with Date1 <> Date2. Do not keep Value-as-is, but calculate the new value as follows: Value - applymap('MapDate2Value', Date1 & '|' & period & '|' & Product, 0) Or with the complete context:

MapDate2Value19DaysOn:

MAPPING LOAD Date1 &'|' & period & '|' & Product as A, Value AS B

RESIDENT YourOriginalFactsTable

WHERE Date1 = Date2;

NewFactsTable:

LOAD Date1, Date2, period, Product,

IF (Date1 <> Date2,

Value - applymap('MapDate2Value', Date1 & '|' & period & '|' & Product, 0),

applymap('MapDate2Value19DaysOn', date(Date1 + 19) & '|' & period & '|' & Product, 0) - Value) AS Value

RESIDENT YourOriginalFactsTable;

Best,

Peter

Re: Calculation possible in Scripting?

Wed, 29 Oct 2014 06:29:12 GMT

HI Peter,

I found the issue.

It is working fine when did some format to the dates.

Thanks for your support and the help