Fill empty records with contents from above

Anonymous — Mon, 19 Nov 2018 21:23:11 GMT

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

Re: Fill empty records with contents from above

sunny_talwar — Mon, 19 Nov 2018 21:29:04 GMT

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;

topic Fill empty records with contents from above in App Development

Fill empty records with contents from above

Re: Fill empty records with contents from above