topic Re: Sorting of concatenated expression based on timestamp and position in QlikView

Sorting of concatenated expression based on timestamp and position

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sorting of concatenated expression based on timestamp and position

Tue, 26 Jul 2016 18:35:31 GMT

if is the table use the tab sort...

use can use more fields for sort

Re: Sorting of concatenated expression based on timestamp and position

santiago_respane — Tue, 26 Jul 2016 20:07:53 GMT

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?

Re: Sorting of concatenated expression based on timestamp and position

maheshkuttappa — Tue, 26 Jul 2016 20:25:36 GMT

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

MAP:

Mapping LOAD * INLINE [

Position, Number

Assistor,2

Converter,3

Initiator,1];

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;

Re: Sorting of concatenated expression based on timestamp and position

Wed, 27 Jul 2016 05:55:15 GMT

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.

Re: Sorting of concatenated expression based on timestamp and position

Wed, 27 Jul 2016 07:23:50 GMT

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

Re: Sorting of concatenated expression based on timestamp and position

Wed, 27 Jul 2016 11:39:42 GMT

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 this

Thanks alot for the code

Re: Sorting of concatenated expression based on timestamp and position

maheshkuttappa — Wed, 27 Jul 2016 14:24:47 GMT

can you share your QVW ?

Re: Sorting of concatenated expression based on timestamp and position

Wed, 27 Jul 2016 14:29:10 GMT

Please have a look

Re: Sorting of concatenated expression based on timestamp and position

maheshkuttappa — Wed, 27 Jul 2016 16:21:52 GMT

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