topic Re: Filter Range Data for Accounts in Script in QlikView

Solved

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Solved

Re: Filter Range Data for Accounts in Script

sunny_talwar — Fri, 27 May 2016 15:09:46 GMT

Like this?

Table:

LOAD ACCOUNT,

ACTIVITY,

GROUP1,

GROUP2

FROM

[https://community.qlik.com/thread/218639]

(html, codepage is 1252, embedded labels, table is @1);

Link:

LOAD Distinct *;

LOAD *,

If(ACTIVITY = '2XX', 199 + IterNo(),

If(ACTIVITY = '3XX', 299 + IterNo(), ACTIVITY)) as ACTIVITY_LINK

Resident Table

While IterNo() <= 100;

DROP Table Table;

FACT:

Right Keep (Link)

LOAD ACCOUNT,

ACTIVITY as ACTIVITY_LINK,

AMOUNT

FROM

[https://community.qlik.com/thread/218639]

(html, codepage is 1252, embedded labels, table is @2);

Re: Filter Range Data for Accounts in Script

microwin88x — Fri, 27 May 2016 15:22:32 GMT

Thank you Sunny T!

Is there any way to omit those values for example Rows 4/5/12/etc?

Because those are amounts that I not need.

Re: Filter Range Data for Accounts in Script

sunny_talwar — Fri, 27 May 2016 15:26:57 GMT

Try this:

Table:

LOAD ACCOUNT,

ACTIVITY,

GROUP1,

GROUP2

FROM

[https://community.qlik.com/thread/218639]

(html, codepage is 1252, embedded labels, table is @1);

Link:

LOAD Distinct *;

LOAD *,

If(ACTIVITY = '2XX', 199 + IterNo(),

If(ACTIVITY = '3XX', 299 + IterNo(), ACTIVITY)) as ACTIVITY_LINK

Resident Table

While IterNo() <= 100;

DROP Table Table;

Left Join(Link)

LOAD ACCOUNT,

ACTIVITY as ACTIVITY_LINK,

AMOUNT

FROM

[https://community.qlik.com/thread/218639]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD ACCOUNT,

ACTIVITY,

GROUP1,

GROUP2,

Max(AMOUNT) as AMOUNT

Resident Link

Group By ACCOUNT, ACTIVITY, GROUP1, GROUP2;

DROP Table Link;