topic Re: Derive New Field in QlikView

Derive New Field

alvinford — Mon, 02 Dec 2013 13:23:03 GMT

Hi All,

I have the following table sorted by DateAndTime field and can be grouped by IDLOC column.

IDLOC	DateAndTime	UpdatedDateandTime
P01	20131120080000
P01	20131120083000	20131120150000
P01	20131121090000
P03	20131201100000
P03	20131201140000	20131202030000
P03	20131201190000

My requirement is I need to compare the UpdatedDateandTime with DateAndTime i.e. UpdatedDateandTime has to be compared with

Previous(DateAndTime), DateAndTime and Next(DateAndTime) for the same group IDLOC. Based on the comparision need to derive New Field indicating the Flag.

If the UpdatedDateandTime is Greater Than DateAndTime then 1 else 0. After deriving the table will be as below.

IDLOC	DateAndTime	UpdatedDateandTime	NewField
P01	20131120080000
P01	20131120083000	20131120150000	1
P01	20131121090000
P03	20131201100000
P03	20131201140000	20131202030000	0
P03	20131201190000

Thanks in Advance..

Regards,

Alvin.

Re: Derive New Field

sivarajs — Mon, 02 Dec 2013 13:43:07 GMT

if(num(DateAndTime)>num(UpdatedDateandTime),0,1)

Re: Derive New Field

Mon, 02 Dec 2013 14:04:28 GMT

First convert your date fields into Timestamps and then compare. Please try like below:

IF(NUM(TIMESTAMP#(DateAndTime,'YYYYMMDDhhmmss')) > NUM(TIMESTAMP#(UpdatedDateandTime,'YYYYMMDDhhmmss')) , 0 ,1) AS NewField

Re: Derive New Field

alvinford — Mon, 02 Dec 2013 14:13:51 GMT

Hi Dathu,

Thanks for your reply.

I want to compare with the Previous and Next and Same row , if I do as suggested by you , it will only compare the same row. Which is not my requirement.

Regards,

Alvin.

Re: Derive New Field

Mon, 02 Dec 2013 14:20:50 GMT

You can to use the functions "below" and "above" for to valuate the preceeding and following row.

Regards

Ciro

Re: Derive New Field

saumyashah90 — Mon, 02 Dec 2013 14:21:25 GMT

You can use previous function in Script

if(Num(TIMESTAMP#(UpdatedDateandTime,'YYYYMMDDhhmmss'))=Previous(NUM(TIMESTAMP#(DateAndTime,'YYYYMMDDhhmmss'))),1,0) as NewField

Re: Derive New Field

alvinford — Mon, 02 Dec 2013 14:44:56 GMT

Hi Saumya

Thanks for your reply.

I want to compare with the Previous and Next and Same row , if I do as suggested by you , it will only compare the same row. Which is not my requirement.

Regards,

Alvin.

Re: Derive New Field

Mon, 02 Dec 2013 15:24:29 GMT

Please, see the attachment
I hope reply your problem with solution...

Regards

Re: Derive New Field

alvinford — Mon, 02 Dec 2013 16:07:47 GMT

Hi Ciro,

Thank you for your valuable reply. The solution which you have provided is correct, but i want to perform at the script level as I want to use the field for other calculation.

Regards,

Alvin.

Re: Re: Derive New Field

Mon, 02 Dec 2013 16:42:41 GMT

Well, see now the attachment...

Re: Re: Derive New Field

Mon, 02 Dec 2013 17:06:43 GMT

Please find the attached application:

DATA:

load IDLOC, Num(Timestamp#(DATE_TIME,'YYYYMMDDhhmm')) AS DATE_TIME, Num(Timestamp#(UPDATE_TIME,'YYYYMMDDhhmm')) AS UPDATE_TIME

inline [

IDLOC,DATE_TIME, UPDATE_TIME

P01,201311200800,

P01,201311200830,201311201500

P01,201311210900,

P02,201312011000,

P02,201312011400,201312020300

P02,201312011900,

];

Left Join (DATA)

LOAD IDLOC, IF(Max(UPDATE_TIME) > Max(DATE_TIME) ,0,1) AS FLAG

Resident DATA Group by IDLOC;

Re: Derive New Field

alvinford — Tue, 03 Dec 2013 01:52:28 GMT

Hi Ciro ,

Once again thanks for your valuable reply. I think I have to modify my query i.e. I have the following records

RowNo	IDLOC	DateAndTime	UpdatedDateandTime
1	P01	20131120080000
2	P01	20131120083000	20131120150000
3	P01	20131121090000
4	P01	20131121120000
5	P03	20131201100000
6	P03	20131201140000	20131202030000
7	P03	20131201190000
8	PO3	20131201230000

for the IDLOC PO1 i have to compare the RowNo 2 with 1 and 3 and maintain a flag at RowNo 2 and same for the IDLOC PO3 I need to compare RowNo 6 with 5 and 7 and maintain the Flag in 6. Which will be as below

RowNo	IDLOC	DateAndTime	UpdatedDateandTime	NewField
1	P01	20131120080000
2	P01	20131120083000	20131120150000	1
3	P01	20131121090000
4	P01	20131121120000
5	P03	20131201100000
6	P03	20131201140000	20131202030000	0
7	P03	20131201190000
8	PO3	20131201230000

Regards,

Alvin.

Re: Derive New Field

Tue, 03 Dec 2013 08:26:02 GMT

Hi, with my script you compare each line for each IDLOC with the same value of DAtaAndTime (replicate on each line for the join) , the result is a NewField with value for each line, if you want to have a single value on the same line of UpdateDateandTime , i think is necessary another load .....resident.... with a condition i.e.:IF (LEN(UpdatDAteandTime)>1,NewField) and you obtain value for NewField only on the row where ther is the value of UpdateDateandTIme.
Regards