topic Re: Front end expression in QlikView

Front end expression

MindaugasBacius — Thu, 25 May 2017 10:57:23 GMT

Hello,

I would like to ask for advice.

I do have table:

I want to sum the Sales that is Type P and Category D except if aggregation of Date and ID Type F Category is not L.

The end result should be:

Date, ID, Sum(Sales)

2017.01.01, 111, 1

Thank you!

Re: Front end expression

sunny_talwar — Thu, 25 May 2017 11:07:38 GMT

May be this

=Sum({<Type = {'P'}, Category = {'D'}>-<Key = {"=Count(DISTINCT {<Type = {'F'}, Category = {'L'}>} Key) > 0"}>}Sale)

Where Key is created in script like this

Table:

LOAD Date&ID as Key,

LOAD Date,

ID,

Type,

Category,

Sale

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Front end expression

MindaugasBacius — Thu, 25 May 2017 11:20:19 GMT

I definitely need to improve my set analysis skills.

As always you're unbeatable

Re: Front end expression

Kushal_Chawda — Thu, 25 May 2017 11:40:25 GMT

I know you got the answer but just to give you script solution

Data:

LOAD Category,

Date,

ID,

Sale,

Type

FROM

[Book.xls]

(biff, embedded labels, table is Sheet1$);

Left Join(Data)

LOAD Distinct Date,

ID,

1 as Flag

Resident Data

Where (Type='F' and Category='L');

Final:

NoConcatenate

LOAD *

Resident Data

where IsNull(Flag) and Type<>'F';

DROP Table Data;