Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting of concatenated expression based on timestamp and position

Hi ,

I have to concatenate channel names for each user based on time and its position. I have around 80 million records in this format

User Name

Channel Name

Position

Timestamp

Neha Jain

Social

Initiator

11/7/2015 6:01

Neha Jain

Email

Assistor

11/7/2015 6:02

Neha Jain

Social

Assistor

11/7/2015 6:03

Neha Jain

Direct

Converter

11/7/2015 6:04

Neha Jain

Social

Initiator

11/7/2015 6:05

Neha Jain

Display Ad

Assistor

11/7/2015 6:06

Neha Jain

Email

Converter

11/7/2015 6:07

Ankit  Jain

Email

Initiator

11/7/2015 6:08

Ankit  Jain

Social

Assistor

11/7/2015 6:09

Ankit  Jain

Email

Converter

11/7/2015 6:10

now i want the result like this

UserName   Path

Neha Jain    Social->Email->Social->Direct

Neha jain     Social ->Display AD -> Email

Ankit Jain     Email->Social ->Email

Initiator should be first, Assistor in between and converter last sorted by time also

Please help

9 Replies
Not applicable
Author

if is the table use the tab sort...

use can use more fields for sort

santiago_respane
Specialist
Specialist

Hi,

can you elaborate more about how would you aggregate the info?

you don't have any record in the same timestamp so it wont aggregate as you want...

When you say

Neha Jain    Social->Email->Social->Direct

Neha jain     Social ->Display AD -> Email

How do you make this aggregation? Also where do you get the Direct value?

Am i being clear?

maheshkuttappa
Creator II
Creator II

Some thing like this .... ....... ?

MAP:

Mapping LOAD * INLINE [

Position, Number

Assistor,2

Converter,3

Initiator,1];

t:

LOAD

*,

If(len(trim(_InitiatorFlag))=0,Peek(_GroupBy),_InitiatorFlag) as _GroupBy;

LOAD [User Name],

     [Channel Name],

     Position,

     date#(Timestamp,'MM/DD/YYYY hh:mm') as Timestamp ,

     ApplyMap('MAP',Position) as PositionNo,

     If(Position='Initiator',RecNo()) as _InitiatorFlag

FROM

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

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

Final:

LOAD

[User Name],

_GroupBy,

Concat([Channel Name],'>',PositionNo) as Path

Resident t Group by [User Name],_GroupBy Order by Timestamp ;

DROP Table t;

Not applicable
Author

i am new to qlikview and doesn't know where to put your code.

I have ingested data using csv and after that what needs to done.

Not applicable
Author

I will try to elaborate once again.

Every user traverses a path among the channels like social,email,direct,display ad etc. for every transaction

So if you see in the above table , User Neha  has two transaction - first one starts the path from social and ends at direct . and this path will be sorted by time so it started at 6.01 from social and converts at 6.04.

I want to make paths for all the user like that. Hope i am clear this time

Not applicable
Author

I some managed to run your code and it worked also for two records but it introduces null fields in the data.

attached images will give you an idea about thissorting.png

Thanks alot for the code

maheshkuttappa
Creator II
Creator II

can you share your QVW ?

Not applicable
Author

Please have a look

maheshkuttappa
Creator II
Creator II

I have attached the solution , this solution is based on assumption that field Path ID are unique and defines the path