topic Re: Two Column Values from one column source in QlikView

Two Column Values from one column source

Wed, 25 Nov 2020 16:16:04 GMT

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

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,

Re: Two Column Values from one column source

MK_QSL — Tue, 28 Jun 2016 08:10:09 GMT

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
];

Re: Two Column Values from one column source

tamilarasu — Tue, 28 Jun 2016 08:34:02 GMT

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  
];

Re: Two Column Values from one column source

Anonymous — Tue, 28 Jun 2016 08:42:23 GMT

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!!

Re: Two Column Values from one column source

Fri, 01 Jul 2016 03:17:25 GMT

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,

Re: Two Column Values from one column source

Fri, 01 Jul 2016 03:18:20 GMT

Hi Manuel and Tamil,

Appreciate also your insights.

Might used it if I have standard data.

Thanks.

with best regards,