topic Re: Combined two columns into one in QlikView

Combined two columns into one

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I got in my DB this kind of table :

DimTable1:

RegDate

DepositDate

___________

I wont to combined the RegDate and the DepositDate into one column.

there are some customers that don't have DepositDate but all have RegDate.

for example if customers have also DepositDate the new table sholud look like this:

ID Date

1 12/12/06

1 11/11/06

I tried to make full join by change the name of the field to the same name, but it took only one date.

Thanks for any help.

Re: Combined two columns into one

MindaugasBacius — Wed, 15 Jun 2016 09:03:01 GMT

Just use IF statement:

IF(ISNULL(DepositDate), RegDate, DepositDate) as Date

Re: Combined two columns into one

ramasaisaksoft — Wed, 15 Jun 2016 09:13:36 GMT

Hi David,

Load

Id,

RegDate,

if(DepositDate=' ',RegDate,DepositDate) as DepositDate

From

.....xyz......;

Re: Combined two columns into one

avinashelite — Wed, 15 Jun 2016 09:19:02 GMT

try like this

DimTable1:

Id,

RegDate,

DepositDate,

if(len(trim(DepositDate))=0,RegDate,DepositDate) as New_field

if you need to just combine then

RegDate & DepositDate as new_field_name

Re: Combined two columns into one

Anonymous — Wed, 15 Jun 2016 09:31:22 GMT

Hi,

I used the concatenate and it same that working well.

I missing something ? 😕

DimTable:

LOAD Id,

RegDate as Date,

FROM

Concatenate

LOAD CustomerKey,

DepositDate as Date,

FROM

STORE

Re: Combined two columns into one

PrashantSangle — Wed, 15 Jun 2016 09:36:09 GMT

Hi,

try like

Load

RegDate as commonDate

from table ;

concatenate

Load Id

DepositDate as commonDate

from table;

If you want to give some conditions like you say if depositDate is null then RegDate

then you can use

if(len(trim(depositDate))=0,RegDate,depositDate) as commonDate

Regards

Re: Combined two columns into one

PrashantSangle — Wed, 15 Jun 2016 10:22:54 GMT

Hi,

good.

Closed thread.

Regards

Re: Combined two columns into one

Anonymous — Wed, 15 Jun 2016 12:52:09 GMT

Hi,

Thanks everyone I still got one issue hope to get some help from you.

I added two columns Reg and Deposit that get 0 or 1 - ( according to the date- if there is a date get 1 else 0)

the table look like this:

Id Date Reg Deposit

1 1/1/15 1 0

1 1/1/15 0 1

In case it at the same date I want it to be like this :

Id Date Reg Deposit

1 1/1/15 1 1

Any help will be great

Thanks

Re: Combined two columns into one

PrashantSangle — Wed, 15 Jun 2016 13:27:20 GMT

Hi,

instead of concatenate try with outer Join

Regards

Re: Combined two columns into one

Anonymous — Thu, 16 Jun 2016 11:27:53 GMT

Hi,

Thanks for your reply.

I used this script and still i got the same problem as i mention above.

Measures:

LOAD ID,

RegDate as Date,

if(isnull(CustomerRegistrationDate),0,1) as Leads

FROM [..\Qvd\DimCustomersTemp2.Qvd] (qvd);

outer join

LOAD ID,

DepositDate as Date,

if(isnull(CustomerFirstDepositDate),0,1) as DP

FROM [..\Qvd\DimCustomersTemp2.Qvd] (qvd);

STORE Measures into ..\Qvd\Measures.Qvd;

drop Table Measures;

Measures:

LOAD ID,

Date,

Leads,

DP,

MakeDate(Year(Date),month(Date),day(Date)) as DateTime

FROM [..\Qvd\Measures.Qvd] (qvd);

STORE Measures into ..\Qvd\Measures.Qvd;

what I missing ? 😕

Re: Combined two columns into one

PrashantSangle — Thu, 16 Jun 2016 13:12:26 GMT

Hi,

I used below code it work fine at my end

Test:

LOAD * INLINE [

Id, RegDate,DepDate

1, 1/1/15,1/1/15

];

Final:

Load Id,

RegDate as date,

if(not IsNull(RegDate),1,0) as Lead

Resident Test;

join

Load Id,

DepDate as date,

if(not IsNull(DepDate),1,0) as DP

Resident Test;

drop Table Test;

Regards

Re: Combined two columns into one

Anonymous — Thu, 16 Jun 2016 13:19:15 GMT

I used different code, but with a same logic and it's worked.

Thanks for the help