topic Re: Loading Calculated Values in QlikView

Loading Calculated Values

Fri, 17 May 2013 03:11:28 GMT

Hello

I have the following code.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
TRANSACTION:
LOAD YYYYMM, 
     USER_ID, 
     CARD_CODE, 
     AMOUNT, 
     T1, 
     T2, 
     R1, 
     R2
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
TMODE:
LOAD
USER_ID,
IF(T1>0,'T1') AS TMODE
RESIDENT TRANSACTION;
LOAD
USER_ID,
IF(T2>0,'T2') AS TMODE
RESIDENT TRANSACTION;
RCODE:
LOAD
USER_ID,
IF(R1>0,'R1') AS RCODE
RESIDENT TRANSACTION;
LOAD
USER_ID,
IF(R2>0,'R2') AS RCODE
RESIDENT TRANSACTION;
AMOUNT_PERCENTAGE:
LOAD
USER_ID,
IF(AMOUNT >0, ((AMOUNT/Max(AMOUNT))*100), 0) AS AMOUNT_PERCENTAGE
RESIDENT TRANSACTION;

I am creating a new field to exisiting Loaded Table TRANSACTION

The New Field is called AMOUNT_PERCENTAGE. It is a calculated field from prviously loaded table.

Basically if the amount is more than 0 I want to calculated the Percentage from the Max(Amount)

Can you help me what misatke I am making?

Thank you

Re: Loading Calculated Values

kumarnatarajan — Fri, 17 May 2013 04:38:16 GMT

Hi,

Try this code for "AMOUNT_PERCENTAGE" Table

MaxMap:

Mapping LOAD
USER_ID,
Max(AMOUNT) AS MAXAMOUNT
RESIDENT TRANSACTION Group by USER_ID;

AMOUNT_PERCENTAGE:
LOAD
USER_ID,
IF(AMOUNT >0, ((AMOUNT/ApplyMap('MaxMap',USER_ID))*100), 0) AS AMOUNT_PERCENTAGE
RESIDENT TRANSACTION;

Re: Loading Calculated Values

Fri, 17 May 2013 05:58:35 GMT

Hi Kumar Natarajan

Thanks for the help. Looks like I am not geeting the correct percentage.

Here are my actual data

Actual Data

attached data_trial_dummy.csv

Outcome of the code based on your suggestion.

outcome.csv

Expected (Manual calculation added a Field):

expected.csv

Request; My company is in the process of buying licence. I will have licence in two weeks. At present I am using personal Licence so I won't able to load any qlikview files for the time being. Thank you

Re: Loading Calculated Values

kumarnatarajan — Fri, 17 May 2013 06:12:34 GMT

Hi,

i don't know why you are useing so many table and conditions. but field name must be unique.

find the attached file.

Re: Loading Calculated Values

Fri, 17 May 2013 10:16:23 GMT

Thanks Kumar. Please refer to the following thread to understand more

http://community.qlik.com/message/346711#346711

You can ignore the conditions of T1,T2, R1,R2 etc. These are meant for some Analysis purpose..

Based on the suggestion by vvandeweerd I am trying to add the calculation in Load function . The expression is not working.

By the way I am unable to open your file test2.qvw as I am using Personal Edition. Licence approval in in pipeline.

Thank you

Re: Loading Calculated Values

kumarnatarajan — Fri, 17 May 2013 10:36:58 GMT

Hi,

Ok i will refer this Url.

Try below mantioned script.

MaxMap:

Mapping
LOAD
     USER_ID,
     max(AMOUNT) as MaxAmount
FROM

data_trial_dummy.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq) Group by USER_ID;

TRANSACTION:

LOAD *,
IF(T1>0,'T1') AS TMODE,
IF(T2>0,'T2') AS TMODE2,
IF(R1>0,'R1') AS RCODE,
IF(R2>0,'R2') AS RCODE2,
IF(AMOUNT >0, ((AMOUNT/ApplyMap('MaxMap',USER_ID))*100), 0) AS AMOUNT_PERCENTAGE;
LOAD YYYYMM,
     USER_ID,
     CARD_CODE,
     AMOUNT,
     T1,
     T2,
     R1,
     R2
FROM
data_trial_dummy.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);