how to get Aggr value based on the Multiple Condition

[brijeshvma]

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'

[Digvijay_Singh]

Try this -

T1:

Load * inline [

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

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;

[brijeshvma]

Hi Digvijay ,

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

Atleast not in script

[Digvijay_Singh]

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.

[Digvijay_Singh]

Could you find this helpful?