Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
JD
New Contributor II

Fill empty records with contents from above

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

1 Solution

Accepted Solutions

Re: Fill empty records with contents from above

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;

View solution in original post

1 Reply

Re: Fill empty records with contents from above

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;

View solution in original post