Need Help

[qv_testing]

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....

[Anonymous]

can you give example for Active / Inactive

what do you want to achieve with the tables for KPI, what means full Count?

[qv_testing]

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?????

[jonathandienst]

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)

[qv_testing]

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

[Anonymous]

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)

[sasikanth]

HI,

please find Attached application

[PradeepReddy]

see the attachment ...