Combined two columns into one

[Anonymous]

Hi,

I got in my DB this kind of table :

DimTable1:

Id

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.

[MindaugasBacius]

Just use IF statement:

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

[ramasaisaksoft]

Hi David,

Load

Id,

RegDate,

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

From

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

[avinashelite]

try like this

DimTable1:

Id,

RegDate,

DepositDate,

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

OR

if you need to just combine then

RegDate & DepositDate as new_field_name

[Anonymous]

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

[PrashantSangle]

Hi,

try like

Load

Id

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

[PrashantSangle]

Hi,

good.

Closed thread.

Regards

[Anonymous]

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

[PrashantSangle]

Hi,

instead of concatenate try with outer Join

Regards

[Anonymous]

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 ? 😕