topic Re: calculation in load or expression in QlikView

calculation in load or expression

Fri, 22 Apr 2016 16:06:22 GMT

Hi Guys,

how to get below mentioned output for this input?

TicketNumber	ID	TYPE
100	1	A
100	2	B
100	3	C
100	4	B
100	5	E
200	1	B
200	2	A
200	3	E
300	1	A
300	2	A
300	3	E

Output: I'm expecting Ticketnumber 300 records as a output,why because this ticket went through only ticket A & E as type,but ticket number 100 also went through the A & E but also went through some other types (type C in above data)..so I do not need ticket number 100 & 200 (type B in above data so no need) records in my output.

any help will be appreciated

thanks

Re: calculation in load or expression

sunny_talwar — Fri, 22 Apr 2016 16:31:59 GMT

I see what you mean. This is needed on the front end or script?

Re: calculation in load or expression

sunny_talwar — Fri, 22 Apr 2016 16:37:57 GMT

May be this to create Flag and use the flag in set analysis to display only required TicketNumber

Table:

LOAD TicketNumber,

ID,

TYPE

FROM

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

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

Left Join (Table)

LOAD TicketNumber,

Count(DISTINCT If(not WildMatch(TYPE, 'A', 'E'), TYPE)) as Count,

If(Count(DISTINCT If(not WildMatch(TYPE, 'A', 'E'), TYPE)) = 0, 1, 0) as Flag

Resident Table

Group By TicketNumber;

Re: calculation in load or expression

Gysbert_Wassenaar — Fri, 22 Apr 2016 16:40:39 GMT

So you want to show only the ticket numbers that only have type A and E. If so try this expression:

count({<TicketNumber=P({<TYPE={'A'}>}TicketNumber)>

*<TicketNumber=P({<TYPE={'E'}>}TicketNumber)>

*<TicketNumber=E({<TYPE-={'A','E'}>}TicketNumber)>

}TicketNumber)

Re: calculation in load or expression

Fri, 22 Apr 2016 16:46:37 GMT

many thanks sunny,

seems it will work with my real time data as well.

will test it and mark this shortly.

thank u so much.

Re: calculation in load or expression

maxgro — Fri, 22 Apr 2016 18:35:01 GMT

Maybe another script solution could be

(keep the record where the distinct concat of TYPE field, order by TYPE, is AE

Table:

LOAD TicketNumber,

ID,

TYPE

FROM

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

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

Right Keep (Table)

LOAD TicketNumber

Where Flag;

LOAD

TicketNumber,

Concat(DISTINCT TYPE, '', TYPE) = 'AE' as Flag

Resident Table

Group By TicketNumber;