Two Column Values from one column source

[#1]

Hi QlikCommunity,

I have a problem extracting some information that need to put into a structured data and would like to get the info.

From

Timestamp                     Message

3/30/2016 10:55:36 AM    My Mommy Cook Lunch

3/30/2016 11:55:36 AM    My Mommy Serve Lunch

3/30/2016 12:05:36 PM    My Daddy Eat Lunch

3/30/2016 12:10:36 PM    My Brother Eat Lunch

3/30/2016 1:10:36 PM    My Mommy Wash Dishes

To

Date                HR            Person        Action

3/30/2016     11:00 AM       Mommy      Cook Lunch

3/30/2016     12:00 PM       Mommy      Serve Lunch

3/30/2016     12:00 PM       Daddy        Eat Lunch

3/30/2016     12:00 PM       Brother       Eat Lunch

3/30/2016       1:00 PM       Mommy     Wash Dishes

Can someone help me here?

Thanks in advance,

with best reagrds,

MJ

[MK_QSL · Accepted solution]

SET TimeFormat='hh:mm:ss TT';
SET DateFormat='M/DD/YYYY';
SET TimestampFormat='M/DD/YYYY h:mm:ss TT';

Data:
Load
  TimeStamp(Timestamp) as Timestamp,
  Date(Floor(Timestamp)) as Date,
  Time(Round(Frac(Timestamp),Time#('01:00','hh:mm'))) as HR,
  Pick(WildMatch(Message,'*Mommy*','*Daddy*', '*Brother*'),'Mommy','Daddy','Brother') as Person,
  Pick(WildMatch(Message,'*Cook Lunch','*Serve Lunch', '*Eat Lunch','*Wash Dishes'),'Cook Lunch','Serve Lunch','Eat Lunch','Wash Dishes') as Action
Inline
[
  Timestamp,                     Message
  3/30/2016 10:55:36 AM,    My Mommy Cook Lunch
  3/30/2016 11:55:36 AM,    My Mommy Serve Lunch
  3/30/2016 12:05:36 PM,    My Daddy Eat Lunch
  3/30/2016 12:10:36 PM,    My Brother Eat Lunch
  3/30/2016 1:10:36 PM,    My Mommy Wash Dishes
];

[tamilarasu]

Hi,

May be  this,

Table:
Load Date(Date#(Timestamp,'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY') as Date,
Time(Round(Date#(Timestamp,'MM/DD/YYYY hh:mm:ss TT'),1/24),'hh:mm TT') as HR,
Replace(Left(Message, Index(Message,' ',2)),'My ','') as Person,
Right(Message, Len(Message)-Index(Message,' ',2)) as Action;
Load * Inline  
[  
  Timestamp,                     Message  
  3/30/2016 10:55:36 AM,    My Mommy Cook Lunch  
  3/30/2016 11:55:36 AM,    My Mommy Serve Lunch  
  3/30/2016 12:05:36 PM,    My Daddy Eat Lunch  
  3/30/2016 12:10:36 PM,    My Brother Eat Lunch  
  3/30/2016 1:10:36 PM,    My Mommy Wash Dishes  
];

[Anonymous]

Hi MJ,

More dynamic:

Final:
LOAD
    Date(Floor(Timestamp),'MM/DD/YYYY')    as Date,
    Time(Round(Frac(Timestamp),Time#('01:00','hh:mm')),'hh:mm TT') as Hour,
    SubField(Message,' ',2)    as Person,
    Trim(Right(Message, (Len(Message)-(index(Message,SubField(Message,' ',2))+Len(SubField(Message,' ',2)))))) as Task
RESIDENT Data;

Regards!!

[#5]

Hi Manish,

This is what I really need in some of my data.

My example is a bit generic, but the action sometimes need to dig into.

Thanks,

with best regards,

MJ

[#6]

Hi Manuel and Tamil,

Appreciate also your insights.

Might used it if I have standard data.

Thanks.

with best regards,

MJ