6 Replies Latest reply: May 4, 2012 8:38 AM by Miroslav Žebrák

# Moving calculated dimension to script

Hi, please, how can I move this calculated dimension to script part?

=aggr (if(sum (Amount) <=200,'1. до 200 грн.',

if(sum (Amount) >200 and sum (Amount)<=1000,'2. 200-1000 грн.',

if(sum (Amount)>1000 and sum (Amount)<=5000,'3. 1000-5000 грн.',

if(sum (Amount)>5000,'4. более 5000 грн.')))), EntityId)

"Amount" is from table "AmountRank" and "EntityId" is in other 3 tables.

I put to script only this for now. I did column "Interval" with theese intervals.

if (Amount <=200,'1. до 200 грн.',

if(Amount >200 and Amount <=1000,'2. 200-1000 грн.',

if(Amount >1000 and Amount <=5000,'3. 1000-5000 грн.',

if (Amount >5000,'4. более 5000 грн.')))) as Interval,

Thank you!

• ###### Re: Moving calculated dimension to script

Hi

You'd first need to get both Amount and EntityId in the same table by joining the tables together. Once you have this you can use group by and your nested if functions like this:

*,

if(sum (Amount_sum) <=200,

'1. до 200 грн.'

,

if(sum (Amount) >200 and sum (Amount_sum)<=1000,

'2. 200-1000 грн.'

,
if(sum (Amount)>1000 and sum (Amount_sum)<=5000,

'3. 1000-5000 грн.'

,

if(sum (Amount_sum)>5000,'4.

более 5000 грн.'

)

)

)

) AS Bucket

;

sum(Amount) AS Amount_sum,

EntityId

RESIDENT myTable

GROUP BY EntityId;

Regards

Matt

• ###### Moving calculated dimension to script

Thanks Matt, I am sure it will work!

But I have "EntityId" in several tables.

Table_AmountRank:

[Amount TypeId],

[Amount Type],

CurrencyId,

Amount,

[Amount without conversion],

PaymentCount,

PaymentDate,

Day,

Month,

Year,

AmountRank

FROM ...

Table_CigEntity:

[Entity Type]

FROM ...

Table_Relational:

EntityId,

RelationType

FROM ...

PostalCode,

EntityId,

id,

FROM ...

I tried to do:

Join (Table_AmountRank)

[Entity Type]

FROM ...

but QV returned system error with out of memory. I have 8GB RAM.

• ###### Re: Moving calculated dimension to script

The problem lies witht he fact that your Table_AmountRank doesn't seem to have any field within it that matches a field within Table_CigEntity which you are trying to join to it. If there is no relationship in the data you can't expect QlikView to invent one for you. The out of memory is because QlikView is calculating the cartesian product of the 2 tables, all records in one relating to all records in the other, and will result in a table containing a number of records equal to the number of records in the 2 tables multiplied together.

Does the RelationType field relate to [Amount Type] by any chance?

• ###### Moving calculated dimension to script

Thanks you very much for explanation. I think, there is no chance.

And about IF, which you posted before. It should not be like this? Because SUM can be used only with Group By. And we are creating Amount_sum below in resident table.

if(Amount_sum <=200,'1. до 200 грн.',

if(Amount_sum >200 and Amount_sum <=1000,'2. 200-1000 грн.',

if(Amount_sum >1000 and  Amount_sum <=5000,'3. 1000-5000 грн.',

if(Amount_sum >5000,'4.более 5000 грн.')))) AS Interval,

This is my whole script now:

Table_AmountRank:

[Amount TypeId],

[Amount Type],

CurrencyId,

Amount,

//     if(Amount_sum <=200,'1. до 200 грн.',

//     if(Amount_sum >200 and Amount_sum <=1000,'2. 200-1000 грн.',

//     if(Amount_sum >1000 and  Amount_sum <=5000,'3. 1000-5000 грн.',

//     if(Amount_sum >5000,'4.более 5000 грн.')))) AS Interval,

[Amount without conversion],

PaymentCount,

PaymentDate,

Day,

Month,

Year,

AmountRank

FROM

Table_AmountRank.QVD

(qvd);

//// must have joined table

//Interval_test:

//

//  sum(Amount) AS Amount_sum,

//    EntityId

//

//RESIDENT Table_AmountRank

//GROUP BY EntityId;

Table_MinOverdue:

MinOverdueAmount,

OverdueNum

FROM

Table_MinOverdue.QVD

(qvd);

Table_Contract:

[Contract TypeId],

[Contract Type]

FROM

Table_Contract.QVD

(qvd);

Table_Records:

ContractId

FROM

Table_Records.QVD

(qvd);

Table_Dates:

[Contract Date],

[Date TypeId],

[Date Type]

FROM

Table_Dates.QVD

(qvd);

Table_Lookup:

[Contract Status]

FROM

Table_Lookup.QVD

(qvd);

Table_CigEntity:

[Entity Type]

FROM

Table_CigEntity.QVD

(qvd);

Table_Relational:

EntityId,

RelationType

FROM

Table_Relational.QVD

(qvd);

// SQL SELECT Id as LookupId,

//    NameNative

//FROM "CigSuite_v3_UA".dbo."c_ge_l_LookupValue";

PostalCode,

EntityId,

id,

FROM

(qvd);

Table_Regions:

Regions_Lable,

Post_index as 'PostalCode'

FROM

Ukraine_final.xls

(biff, embedded labels, table is Post_index\$);

• ###### Re: Moving calculated dimension to script

The code I posted has 2 load statements on a single statement. This is known as a "proceeding load on a proceeding load" and allows you to do multiple stemps of data transformation.

Imagine the below code works from the bottom up:

*,

if(sum (Amount_sum) <=200,

'1. до 200 грн.'

,

if(sum (Amount) >200 and sum (Amount_sum)<=1000,

'2. 200-1000 грн.'

,
if(sum (Amount)>1000 and sum (Amount_sum)<=5000,

'3. 1000-5000 грн.'

,

if(sum (Amount_sum)>5000,'4.

более 5000 грн.'

)

)

)

) AS Bucket

;

sum(Amount) AS Amount_sum,

EntityId

RESIDENT myTable

GROUP BY EntityId;

The data is first retrieved from the resident table and the Amount_sum field is calculated by grouping on Entity. This is then passed up into the above load statement which uses the if functions to create the Bucket field based on the Amount_sum that has been calculated below.

Matt

• ###### Moving calculated dimension to script

I am sorry, I didn't know.. Ok, then. What I must do now is to get EntityId to Amount. Is that right?

This is my data model: