Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friend
I have the below two tables:
1/ Facts Table:
collateral_id | collateral_amount |
---|---|
1 | $200 |
2 | $200 |
3 | $200 |
2/ Ageing Table
bucket | collateral_id | age |
---|---|---|
June/2017 | 1 | 30 |
June/2017 | 2 | 55 |
June/2017 | 3 | 60 |
July/2017 | 1 | 25 |
In the visuals I want to have the following output (by bucket)
bucket | Amount | Age 0-30 | Age 30-60 |
---|---|---|---|
June/2017 | 200+200+200=600 | 200 | 200 |
Jul/2017 | 200 | 200 | 0 |
for the amount: sum(collateral_amount)
for the Age: sum({<age={">0<30"}> collateral_amount}
Problem & question:
the result is correct only when i select the dimenstion in the output table. the result is incorrect if i do not select the dimension
So when i select June/2017, the measure "Amount, Age0-30 and Age 30-60" and recalculated and they become correct.
How can i obtain the correct result without the need to select the dimension ?
(note that it is not possible to connect merge the two table in a single one)
Thanks
Hi Bassam,
Please find attached application on the data you shared. Hope this helps.
Regards,
Akshaya
Hi,
Please find below script and Expression which was used to calculate the desired result.
Note: I haven't linked and marge the two tables.
Data Load Script:
FactsTable:
Load * inline [
collateral_id1, llateral_amount
1,$200
2,$200
3,200
];
ageing_table:
Load * inline [
bucket,collateral_id,age
June/2017,1,30
June/2017,2,55
June/2017,3,60
July/2017,1,25
];
2. On Chart Expression i have used calculated dimension in order to populate the bucket
Dimension :=if(match(collateral_id,collateral_id1),bucket)
Expression: Age 0-30 : sum({< age = {">0 <= 30 "}>}llateral_amount)
Age 30 -60: sum({< age = {">30 <=60 "}>}llateral_amount)
if you found this helpful please mark helpful or correct.
Regards,
Bhasker Kumar
hi Akshaya,
Tk you
how can i import qlikview app to qlik sense ?
Thank you bhasker it worked
Can you explain what is the rationale ? what was wrong and how you fixed it ?
Is this the only way?
I would have like to keep the two table connected through collateral_id
thanks
Hi Bassam,
You can use the logic used in the app to your Qliksense.
I have created a extra column in data using class function and using it in the expression.
Script :
LOAD * INLINE [
collateral_id, collateral_amount
1, 200
2, 200
3, 200
];
T:
Load * INLINE [
bucket, collateral_id, age
June/2017, 1, 30
June/2017, 2, 55
June/2017, 3, 60
July/2017, 1, 25
];
NoConcatenate
TF:
LOAD *,class(age,31,'Age') as AgeGroup Resident T;
Drop Table T;
Front End
Expression - sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 0 - 30
- sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 31 - 62
Hope this helps.
Regards,
Akshaya
Hi,
You can connect both the table through collateral_id.
I haven't faced any issue just used in line load and required expression, there may be issue with the data formatting connected dimension or used dimensions in your chart.
for the age bucking you can either use front expression that i have used or in back-end you can use Class function.
i have used calculated dimensions: if(match(collateral_id,collateral_id1), bucket) , match function return true when first table collateral_id values matched collateral_id1 then it will return the corresponding bucket in dimension.
if you found this helpful, please mark correct or helpful.
Regards,
Bhasker Kumar