Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Calculation possible in Scripting?

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

Tags (1)
1 Solution

Accepted Solutions

Re: Re: Calculation possible in Scripting?

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

11 Replies
Not applicable

Re: Calculation possible in Scripting?

Hi,

you can use the below.

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

Not applicable

Re: Calculation possible in Scripting?

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?

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.

Not applicable

Re: Calculation possible in Scripting?

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?

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)

Not applicable

Re: Calculation possible in Scripting?

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?

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

Not applicable

Re: Calculation possible in Scripting?

HI Peter,

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

BR,

Not applicable

Re: Calculation possible in Scripting?

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?

BR

Community Browser