topic Re: Need Help in QlikView

Need Help

Nagaraju_KCS — Wed, 28 Oct 2015 07:48:06 GMT

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

Re: Need Help

Anonymous — Wed, 28 Oct 2015 07:52:46 GMT

can you give example for Active / Inactive

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

Re: Need Help

Nagaraju_KCS — Wed, 28 Oct 2015 07:53:57 GMT

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

Re: Need Help

jonathandienst — Wed, 28 Oct 2015 07:56:07 GMT

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)

Re: Need Help

Nagaraju_KCS — Wed, 28 Oct 2015 08:00:09 GMT

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

Re: Need Help

Anonymous — Wed, 28 Oct 2015 08:09:22 GMT

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)

Re: Need Help

sasikanth — Wed, 28 Oct 2015 08:10:22 GMT

HI,

please find Attached application

Re: Need Help

PradeepReddy — Wed, 28 Oct 2015 10:08:59 GMT

see the attachment ...