topic Moving calculated dimension to script in QlikView

Moving calculated dimension to script

Fri, 04 May 2012 08:41:40 GMT

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

Fri, 04 May 2012 08:59:34 GMT

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:

LOAD

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

;

LOAD

sum(Amount) AS Amount_sum,

EntityId

RESIDENT myTable

GROUP BY EntityId;

Regards

Matt

Moving calculated dimension to script

Fri, 04 May 2012 09:30:08 GMT

Thanks Matt, I am sure it will work!

But I have "EntityId" in several tables.

Table_AmountRank:

LOAD RecordId,

[Amount TypeId],

[Amount Type],

CurrencyId,

Amount,

[Amount without conversion],

PaymentCount,

PaymentDate,

Day,

Month,

Year,

AmountRank

FROM ...

Table_CigEntity:

LOAD EntityId,

[Entity Type]

FROM ...

Table_Relational:

LOAD ContractId,

EntityId,

RelationType

FROM ...

Table_Address:

LOAD [Address Id],

PostalCode,

AddressId,

[Address TypeId],

EntityId,

id,

[Address Type]

FROM ...

I tried to do:

Join (Table_AmountRank)

LOAD EntityId,

[Entity Type]

FROM ...

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

Re: Moving calculated dimension to script

Fri, 04 May 2012 11:54:06 GMT

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

Fri, 04 May 2012 12:10:07 GMT

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:

LOAD RecordId,

[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:

//LOAD

// sum(Amount) AS Amount_sum,

// EntityId

//RESIDENT Table_AmountRank

//GROUP BY EntityId;

Table_MinOverdue:

LOAD ContractId,

MinOverdueAmount,

OverdueNum

FROM

Table_MinOverdue.QVD

(qvd);

Table_Contract:

LOAD ContractId,

[Contract TypeId],

[Contract Type]

FROM

Table_Contract.QVD

(qvd);

Table_Records:

LOAD RecordId,

ContractId

FROM

Table_Records.QVD

(qvd);

Table_Dates:

LOAD RecordId,

[Contract Date],

[Date TypeId],

[Date Type]

FROM

Table_Dates.QVD

(qvd);

Table_Lookup:

LOAD RecordId,

[Contract Status]

FROM

Table_Lookup.QVD

(qvd);

Table_CigEntity:

LOAD EntityId,

[Entity Type]

FROM

Table_CigEntity.QVD

(qvd);

Table_Relational:

LOAD ContractId,

EntityId,

RelationType

FROM

Table_Relational.QVD

(qvd);

// SQL SELECT Id as LookupId,

// NameNative

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

Table_Address:

LOAD [Address Id],

PostalCode,

AddressId,

[Address TypeId],

EntityId,

id,

[Address Type]

FROM

Table_Address.QVD

(qvd);

Table_Regions:

LOAD ID_Regions,

Regions_Lable,

Post_index as 'PostalCode'

FROM

Ukraine_final.xls

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

Re: Moving calculated dimension to script

Fri, 04 May 2012 12:25:43 GMT

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:

LOAD

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

;

LOAD

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

Fri, 04 May 2012 12:38:49 GMT

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: