Load Script

[kamielrajaram]

Good day,

I have noticed something quite strange. I have a customer table and customer phone table. In the customer phone table there is a row for each phone type, home, work and cell. What I require is all the details as a single record per customer. I load the customer details, then left join to the customer phone table using where exists custid and for the cell phone type, the do the same for the other phone types. What i have noticed is that if there is no cell phone record, then the work and home number are not populated. I am currently different load orders.

Not sure what I am missing

Thank you

Kamiel

Customers:
Load custid...other_fields... from Customers

Left Join
LOAD
dialingcode as [Phone Home Dialling Code],
phonenum as [Phone Home Number],
      if(isnull(dialingcode) =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Home],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Home';

Left Join
LOAD
dialingcode as [Phone Work Dialling Code],
phonenum as [Phone Work Number],
if(isnull(dialingcode) =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Work],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Work';

Left Join
LOAD Distinct
dialingcode as [Phone Cell Dialling Code],
phonenum as [Phone Cell Number],
      if(isnull(dialingcode) =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Cell],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Cell';

[sujeetsingh]

can you give a sample or explain in more details

[Gysbert_Wassenaar]

Something like this:

Customers:

Load CustomerID, ...other_fields... from Customers

join load CustomerID, PhoneNumber as Home

from CustomerPhoneTable

where PhoneType = 'Home';

join load CustomerID, PhoneNumber as Work

from CustomerPhoneTable

where PhoneType = 'Work';

join load CustomerID, PhoneNumber as Cell

from CustomerPhoneTable

where PhoneType = 'Cell';

Change field names and the values in the where clauses to whatever matches your situation.

[Anonymous]

Check the attached file, if it helps you,

or your requirement is somthing different, soplease attach a sample file...

Regards

Nitin

[kamielrajaram]

Customers:
Load custid...other_fields... from Customers

Left Join
LOAD
dialingcode as [Phone Home Dialling Code],
phonenum as [Phone Home Number],
      if(isnulldialingcode =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Home],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Home';

Left Join
LOAD
dialingcode as [Phone Work Dialling Code],
phonenum as [Phone Work Number],
if(isnulldialingcode =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Work],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Work';

Left Join
LOAD Distinct
dialingcode as [Phone Cell Dialling Code],
phonenum as [Phone Cell Number],
      if(isnulldialingcode =-1,'',dialingcode ) & if(isnull(phonenum )=-1,'',phonenum ) as [Phone Cell],
      custid
From [Phone.qvd] (qvd)
Where
current = -1 and
phonetype= 'Cell';