Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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 | Converter | 11/7/2015 6:07 | |
Ankit Jain | Initiator | 11/7/2015 6:08 | |
Ankit Jain | Social | Assistor | 11/7/2015 6:09 |
Ankit Jain | 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
if is the table use the tab sort...
use can use more fields for sort
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?
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;
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.
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
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
can you share your QVW ?
Please have a look
I have attached the solution , this solution is based on assumption that field Path ID are unique and defines the path