Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
From the Data Load Editor if the ‘Address’ field has contents but the ‘Name’ and ‘Number’ fields are blank I want to copy the contents from the records above and paste them into the blank records below.
Here is what I have so far but need some help
Load
Customer:
Name,
Number,
Address,
IF(Len([Name] = 0) and Len([Number] = 0) and Not Len(Address = 0), ,
FROM [lib://Customer/Contact.xlsx]
(ooxml, embedded labels, table is Contact);
Customer Table Before
Name | Number | Address |
Degolyer and MacNaughton | 10009885 | 1044 Degolyer and MacNaughton Blvd |
|
| 1044 Degolyer and MacNaughton Blvd |
Bizmarts | 10006846 | 4174 Bizmarts Blvd |
R&R Group | 10022434 | 6629 R&R Group Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
|
| 6485 Kings Blvd |
|
| 6485 Kings Blvd |
|
| 6485 Kings Blvd |
|
| 6485 Kings Blvd |
C & M Distributors | 10007123 | 623 C & M Distributors Blvd |
Cannon | 10021911 | 2689 Cannon Blvd |
|
| 2689 Cannon Blvd |
|
| 2689 Cannon Blvd |
RKS | 10023447 | 6505 RKS Blvd |
Caprica Resources | 10008602 | 2857 Caprica Resources Blvd |
BigJobs | 10006093 | 591 BigJobs Blvd |
Customer Table After
Name | Number | Address |
Degolyer and MacNaughton | 10009885 | 1044 Degolyer and MacNaughton Blvd |
Degolyer and MacNaughton | 10009885 | 1044 Degolyer and MacNaughton Blvd |
Bizmarts | 10006846 | 4174 Bizmarts Blvd |
R&R Group | 10022434 | 6629 R&R Group Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
Bellbury | 10024260 | 6485 Kings Blvd |
C & M Distributors | 10007123 | 623 C & M Distributors Blvd |
Cannon | 10021911 | 2689 Cannon Blvd |
Cannon | 10021911 | 2689 Cannon Blvd |
Cannon | 10021911 | 2689 Cannon Blvd |
RKS | 10023447 | 6505 RKS Blvd |
Caprica Resources | 10008602 | 2857 Caprica Resources Blvd |
BigJobs | 10006093 | 591 BigJobs Blvd |
Try something like this
Table: LOAD * INLINE [ Name, Number, Address Degolyer and MacNaughton, 10009885, 1044 Degolyer and MacNaughton Blvd , , 1044 Degolyer and MacNaughton Blvd Bizmarts, 10006846, 4174 Bizmarts Blvd R&R Group, 10022434, 6629 R&R Group Blvd Bellbury, 10024260, 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd C & M Distributors, 10007123, 623 C & M Distributors Blvd Cannon, 10021911, 2689 Cannon Blvd , , 2689 Cannon Blvd , , 2689 Cannon Blvd RKS, 10023447, 6505 RKS Blvd Caprica Resources, 10008602, 2857 Caprica Resources Blvd BigJobs, 10006093, 591 BigJobs Blvd ]; FinalTable: NoConcatenate LOAD RowNo() as RowNum, If(Address = Previous(Address) and Len(Trim(Name)) = 0, Peek('Name'), Name) as Name, If(Address = Previous(Address) and Len(Trim(Number)) = 0, Peek('Number'), Number) as Number, Address Resident Table; DROP Table Table;
Try something like this
Table: LOAD * INLINE [ Name, Number, Address Degolyer and MacNaughton, 10009885, 1044 Degolyer and MacNaughton Blvd , , 1044 Degolyer and MacNaughton Blvd Bizmarts, 10006846, 4174 Bizmarts Blvd R&R Group, 10022434, 6629 R&R Group Blvd Bellbury, 10024260, 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd , , 6485 Kings Blvd C & M Distributors, 10007123, 623 C & M Distributors Blvd Cannon, 10021911, 2689 Cannon Blvd , , 2689 Cannon Blvd , , 2689 Cannon Blvd RKS, 10023447, 6505 RKS Blvd Caprica Resources, 10008602, 2857 Caprica Resources Blvd BigJobs, 10006093, 591 BigJobs Blvd ]; FinalTable: NoConcatenate LOAD RowNo() as RowNum, If(Address = Previous(Address) and Len(Trim(Name)) = 0, Peek('Name'), Name) as Name, If(Address = Previous(Address) and Len(Trim(Number)) = 0, Peek('Number'), Number) as Number, Address Resident Table; DROP Table Table;