Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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:
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
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.
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?
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$);
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
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: