topic how to get Aggr value based on the Multiple Condition in QlikView

how to get Aggr value based on the Multiple Condition

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

Hi All ,

I have a Data Like ,

Session

Session	VisitID	Cate	Action
S1	V1	De	Qu
S1	V1	De	Su
S1	V1	De	Qu
S1	V1	De	Qu
S1	V1	De	Tu
S1	V1	Co	Su

Say Example in 1 Session 1 Visitor doing the multiple Event .... I want the Count of Unique Used in One Session if say

When Cate = 'Co' and Action = 'Su' then Count no of time Visitor Where Cat = 'De' and Action 'Qu'

in my example Count Visit is 3 ...Because prior to doing Cate ='Co' and Action =''Su' same visitor did 3 time Cate ='De' and 'Qu'.

Session	VisitID	Cate	Action
S2	V2	De	Qu
S2	V2	Co	Su
S2	V2	De	Qu
S2	V2	De	Qu

in this Answer should Come 1 . Because prior to doing Action Cate='Co' and Action ='Su' Visitor V2 did 1 time Cate='De' and Action='Qu'

Kindly Help me how to get this that in one Session one visitor did how many time Cate = 'De' and Action='Qu Before doing

Cate= 'De' and Action='Su'

Re: how to get Aggr value based on the Multiple Condition

Digvijay_Singh — Wed, 16 Mar 2016 18:55:47 GMT

Try this -

T1:

Load * inline [

Session VisitID Cate Action

S1 V1 De Qu

S1 V1 De Su

S1 V1 De Qu

S1 V1 De Tu

S1 V1 Co Su

S2 V2 De Qu

S2 V2 Co Su

S2 V2 De Qu

S2 V2 De Qu ](delimiter is spaces);

NoConcatenate

T2:

Load Session,

VisitID,

Concat(Cate&Action,',',RecNo()) as CateAction

Resident T1

Group By Session,VisitID

;

T3:

Load Session,

VisitID,

SubstringCount(left(CateAction,index(CateAction,'CoSu')-1),'DeQu') as Count

Resident T2;

Drop table T1,T2;

Re: how to get Aggr value based on the Multiple Condition

brijeshvma — Thu, 17 Mar 2016 06:02:54 GMT

Hi Digvijay ,

Thanks for ur Reply ..But I want to do this by Aggr and set Analysis..

Atleast not in script

Re: how to get Aggr value based on the Multiple Condition

Digvijay_Singh — Thu, 17 Mar 2016 07:02:07 GMT

You can do this, but I had to add RowId field in script as duplicate rows were creating problem, rest of the things managed at front end

Expression used are -

Concat - Concat(Cate&Action,',',RowID)

Count - SubstringCount(left(Concat,index(Concat,'CoSu')-1),'DeQu')

Hope this helps.

Re: how to get Aggr value based on the Multiple Condition

Digvijay_Singh — Fri, 18 Mar 2016 07:30:33 GMT

Could you find this helpful?