calculation in load or expression

[#1]

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

[sunny_talwar]

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

[sunny_talwar]

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;

[Gysbert_Wassenaar]

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)

[#5]

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.

[maxgro]

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;