Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace values from one column to another

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

6 Replies
maneshkhottcpl
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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.

maneshkhottcpl
Partner - Creator III
Partner - Creator III

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

johnw
Champion III
Champion III

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

maneshkhottcpl
Partner - Creator III
Partner - Creator III

Hi,

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

if

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





And refer the attached document , giving output perfectly.

Thanks

Manesh

Not applicable
Author

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