topic Re: Update field valu in load script in QlikView

Update field valu in load script

Tue, 16 Dec 2014 09:21:46 GMT

I have a table with timestamps that I want to load, TimestampStart and TimestampEnd. Sometimes the TimestampEnd has a time that is before the time in TimestampStart, se examples in table below marked red.

I would like to correct this in the load script, so if TimestampStart is less than TimestampEnd, TimestampStart should be updated to TimestampEnd and TimestampEndt should be updated to TimestampStart, the values should change places. Hope anyone can help me to get the script correct.

TimestampStart	TimestampEnd
2010-01-01 00:10	2010-01-01 00:40
2010-01-01 00:10	2010-01-01 00:15
2010-01-01 00:15	2010-01-01 00:20
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:55
2010-01-01 00:30	2010-01-01 00:25
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 12:35
2010-01-01 00:45	2010-01-01 00:30
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:55
2010-01-01 00:30	2010-01-01 00:35

Best Regards

Mårten

Re: Update field valu in load script

tresB — Tue, 16 Dec 2014 09:29:08 GMT

Try like:

Load

If( TimeStampStart>TimeStampEnd, TimeStampEnd, TimeStampStart) as TimeStampStart,

If( TimeStampStart>TimeStampEnd, TimeStampStart, TimeStampEnd) as TimeStampEnd

From <>;

Re: Update field valu in load script

sujeetsingh — Tue, 16 Dec 2014 09:30:31 GMT

you can follow the nested if conditions as per Tresco.

Re: Update field valu in load script

its_anandrjs — Tue, 16 Dec 2014 09:32:16 GMT

Hi,

Load another table

Load

TimestampStart,

TimestampEnd,

if( TimestampStart > TimestampEnd, TimestampEnd,TimestampStart) as TimestampStartNew

From Source;

Ex:-

LOAD

Timestamp#(TimestampStart,'YYYY-DD-MM hh:mm') as TimestampStart,

Timestamp#(TimestampEnd,'YYYY-DD-MM hh:mm') as TimestampEnd;

LOAD * Inline

[

TimestampStart, TimestampEnd

2010-01-01 00:10, 2010-01-01 00:40

2010-01-01 00:10, 2010-01-01 00:15

2010-01-01 00:15, 2010-01-01 00:20

2010-01-01 00:30, 2010-01-01 00:35

2010-01-01 00:30, 2010-01-01 00:55

2010-01-01 00:30, 2010-01-01 00:25

2010-01-01 00:30 , 2010-01-01 00:35

2010-01-01 00:30, 2010-01-01 12:35

2010-01-01 00:45 , 2010-01-01 00:30

2010-01-01 00:30 , 2010-01-01 00:35

2010-01-01 00:30 , 2010-01-01 00:55

2010-01-01 00:30 , 2010-01-01 00:35

];

NoConcatenate

LOAD

TimestampStart, TimestampEnd,

if( TimestampStart > TimestampEnd, TimestampEnd,TimestampStart) as TimestampStartNew,

if( TimestampStart > TimestampEnd, TimestampStart,TimestampEnd) as TimestampEndNew

Resident A;

DROP Table A;

Regards

Anand

Re: Update field valu in load script

Tue, 16 Dec 2014 10:30:22 GMT

Thanks all, now it worked.

Best regards

Mårten