Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

sumitjadhav
Contributor II

I want the Expression for following

I have Two fields in my data ,ie Ticket Type and Ticket Count, i want to write an expression as,

if my ticket count is equal to 10 for ticket type 'p' then the count of ticket must be 1 and if it is greater than 10 ,20,30...........so on,

suppose it is 11-20, then the count of ticket must be 2 and if it is 21-30, then the count of ticket must be 3.please help me to achieve this output.

the following is the reffernce data.

[Ticket Type], [Ticket count]

S1                       5

S2                       11

E1                       15

E2                        6

E3                       7

P                        11

S2

p                        10

P                        22

result must be,

[Ticket Type], [Ticket count] 

S1                       5

S2                       11

E1                       15

E2                        6

E3                       7

P                        2

S2

p                        1

P                        3

please Note:

10 tickets of type 'p'=1 ticket.

remaining type of ticket count it as it is

I wantexpression only for ticket type 'p'.

10 Replies

Re: I want the Expression for following

May be like this in your expression:

If(Upper([Ticket Type]) = 'P',

If([Ticket Count] = 10, 1,

If([Ticket Count] > 10, RangeSum(Div([Ticket Count], 10), 1), 0)), [Ticket Count])

vinieme12
Esteemed Contributor II

Re: I want the Expression for following

Try this, small correction instead of DIV

LOAD *,if(wildmatch([Ticket Type],'p'),ceil(([Ticket count]/10),1),[Ticket count]) as newTicketCount INLINE [

Ticket Type, Ticket count

S1,5

S2,11

E1,15

E2,6

E3,7

P,11

S2,31

p,10

P,22

P,20

P,19

P,31

P,30

P,39

P,40

P,41

];

Re: I want the Expression for following

I guess you concern was the boundary number, can be resolved with this:

If(Upper([Ticket Type]) = 'P', If([Ticket Count] = 10, 1, If([Ticket Count] > 10, RangeSum(Div([Ticket Count]-1, 10), 1), 0)), [Ticket Count]) as NewTicketCount2

vinieme12
Esteemed Contributor II

Re: I want the Expression for following

Yeah that was it,I forgot Div returns int ; but if we use the traditional method for division and then CEIL the result the boundaries are taken care off .

Re: I want the Expression for following

That's right, i agree with you. No use to unnecessarily make it more complicated then what is required

sumitjadhav
Contributor II

Re: I want the Expression for following

I want an single expression for counting the ticket count including the logic of ticket type 'p',how to do it?

sumitjadhav
Contributor II

Re: I want the Expression for following

for all ticket type ,other ticket type ie,S1,S2,E1,E2,E3 with normal count And only ticket type 'P' with that logic.

Re: I want the Expression for following

This should be able to do it:

If(Upper([Ticket Type]) = 'P', If([Ticket Count] = 10, 1, If([Ticket Count] > 10, RangeSum(Div([Ticket Count]-1, 10), 1), 0)), [Ticket Count])

The above will work for Ticket Type = P or when Ticket Type is anything else

Re: I want the Expression for following

Have you tried the expressions already? Are they not working the way you want them? Would you be able to share a sample where it isn't working?

Community Browser