Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have 2 types of count, Active and InActive and 2 KPI's
My Requirement
1. I have 2 tables, in that common fields are Active and remaining are InActive.
2. Another KPI, second table full count.
Suppose like
Table1:
ID, Status, Cost
100, ABC, 100
101, BCA, 200
102, CGS, 300
103, AVX, 400
Table2:
ID, Status1, Cost1
100, abh, 500
101, bvg, 600
104, lkj, 700
105, rty, 800
106, jkl, 900
How to approach the best way....
Thanks in Advance....
can you give example for Active / Inactive
what do you want to achieve with the tables for KPI, what means full Count?
1. I have done somthing like..
First Inner Join for Active Count
again i have loaded Second table using
where not exists(ID) for InActive Count
2. Then Again i have loaded Second table for full count
I have created 2 flags for 2 tables (ActiveFlag and InActiveFlag).
In my chart Dimension and expression.
i have used Count({<Flag={'ActiveFlag'}>} ID)
Count({<Flag={'InActiveFlag'}>} ID)
is this right approach?????
This is one possible way:
Table1:
LOAD ID,
Status As Active.Status,
Cost As Active.Cost
FROM Table1 (....);
MapActive:
Mapping LOAD Distinct ID, 1
Resident Table1;
Table2:
LOAD ID,
Status,
Cost,
ApplyMap('MapActive', ID, 0) As IsActive
FROM Table2 (....);
Join (Table2)
LOAD *
Resident Table1;
DROP Table Table1;
Now you can use the IsActive flag in filters and set expressions. For example:
=Sum({<IsActive = {1}>} Cost)
Hi Rudolf,
Actually i have many fields,
First i have calculate Active Count and InActive Count
Active Count= Common ID fields
InActive Count= Non-Common ID fields
maybe this Approach would fit (for Active/Inactive)?
Table1: load
ID, Status, Cost
100, ABC, 100
101, BCA, 200
102, CGS, 300
103, AVX, 400
Table2:
left join (Table1)
ID, Status1, Cost1, 'Active' as ActiveFlag
from ..
where exists (ID)
Table2:
concatenate load
ID, Status1, Cost1, 'InActive' as ActiveFlag
from ..
where not exists (ID)
HI,
please find Attached application
see the attachment ...