topic Replace values from one column to another in QlikView

Replace values from one column to another

Mon, 14 Feb 2011 13:21:17 GMT

Hello QVer,
I have two columns with date values and I want to replace values:

So let me say:
Every time when there is a value (not NULL) in column=DAT2 it should replace the value
in DAT1 respectively the value in DAT1 should be that of DAT2 when it is not NULL..
The result should be an adapted new column=DAT_NEW.

Sorry but I can't find a solution in this forum.
Can somebody help me? Thanks.

Replace values from one column to another

maneshkhottcpl — Mon, 14 Feb 2011 14:07:31 GMT

Hi,

I am not able to get ur actual problem, but i m trying to solve.

if(isnull(DAT2),DAT1,DAT2) as NEW_DAT

use the expression in load script.

Replace values from one column to another

Mon, 14 Feb 2011 15:13:29 GMT

Thanks Manesh,

Your suggestion only results in a copy of field DAT2. I'm missing the value from DAT1 in the NEW_DAT field.

To illustrate I send a little QVW.

Thanks for another help.

Replace values from one column to another

maneshkhottcpl — Tue, 15 Feb 2011 01:29:30 GMT

Hi,

My above expression gives the result that,

1. If DAT1 and DAT2 both exists then NEW_DAT will be DAT2

2. If DAT2 is null then NEW_DAT will be DAT1

can u explain ur problem again so i can post u the solution.

Thanks

Replace values from one column to another

johnw — Tue, 15 Feb 2011 01:41:47 GMT

I think the problem is just isnull(), which doesn't work properly in some QlikView versions, I think 64 bit? Can't remember exactly. Anyway, this works for me:

INLINE:
LOAD *
,if(len(DAT2),DAT2,DAT1) as NEW_DAT
INLINE [
DAT1, DAT2
01.01.2011, 31.12.2009
15.01.2011,
20.01.2011, 04.01.2011
23.01.2011,
];

Replace values from one column to another

maneshkhottcpl — Tue, 15 Feb 2011 03:16:29 GMT

Hi,

You are right , some time isnull doesnot work. so use the following expression.

(isnull(DAT2) or len(DAT2)<2, DAT1, DAT2) as NEW_DAT

And refer the attached document , giving output perfectly.

Thanks

Manesh

Replace values from one column to another

Tue, 15 Feb 2011 04:50:11 GMT

Hi,

Whenever using len, I prefer to use len(trim(<Field Name>)) as the column may have blank spaces stored in some cased where the data type of the field is text.

Thanks & Best Regards,

Kuldeep Tak