Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
SK28
Creator
Creator
‎2023-03-31 06:54 AM

Fill the empty columns based on above column

Hi

 

I have a data set as shown below

PARENT ID TYPE CHILD ID status COLUM2 COLUMN3
123 ABC 123-V3 done ab sd
    123-V4      
    123-V5      
    123-V6      
    123-V7      
234 bcf 234-v1-15 complete ef ab
    234-v1-16      
    234-v1-17      
456 def 456v1-123-v6-12 WIP gh kl
    456v1-123-v6-13      
    456v1-123-v6-14      
789 ght ghtv1-d complete jk ol
910 ftr 910-fgt complete gh kl

 

Column A = Parent ID 
Column B = Type
Column C = Child ID

Column D = Status

Similarly I have 10+ other columns as shown above,

I want to fill the Column A, B, D based on the Child ID,

Here is the required out put

I have tried Peek & Previous Functions but it's not working Can anyone please help me on this,

Required out:

PARENT ID TYPE CHILD ID status COLUM2 COLUMN3
123 ABC 123-V3 done ab sd
123 ABC 123-V4 done ab sd
123 ABC 123-V5 done ab sd
123 ABC 123-V6 done ab sd
123 ABC 123-V7 done ab sd
234 bcf 234-v1-15 complete ef ab
234 bcf 234-v1-16 complete ef ab
234 bcf 234-v1-17 complete ef ab
456 def 456v1-123-v6-12 WIP gh kl
456 def 456v1-123-v6-13 WIP gh kl
456 def 456v1-123-v6-14 WIP gh kl
789 ght ghtv1-d complete jk ol
910 ftr 910-fgt complete gh kl

 

 

here is the data set:

Can this be achieved?

Hi @aveeeeeee7en it's working only for 3 records, if they blank records are more than 3, it's showing as '-' Null() blank,

else 

Can we achieve this I have child column like this

child
ABC-1013.V2-24187
ABC-1013.V2
ABC-1046.V2-24428
ABC-1046.V2
ABC-1178.V2-25465
ABC-1178.V2
ABC-1229.V2-25925
ABC-1229.V2
ABC-1250.V2-26366
ABC-1250.V2
ABC-1323.V2-27096
ABC-1323.V2
ABC-1365.V2-24243
ABC-1365.V2
ABC-1597.V2-19167
ABC-1597.V2
ABC-1631.V2-25265
ABC-1631.V2
ABC-1701.V2-21053

 

Output should be:

parent
ABC-1013.V2
ABC-1013.V2
ABC-1046.V2
ABC-1046.V2
ABC-1178.V2
ABC-1178.V2
ABC-1229.V2
ABC-1229.V2
ABC-1250.V2
ABC-1250.V2
ABC-1323.V2
ABC-1323.V2
ABC-1365.V2
ABC-1365.V2
ABC-1597.V2
ABC-1597.V2
ABC-1631.V2
ABC-1631.V2
663 Views
0 Likes
6 Replies
aveeeeeee7en
Specialist III
Specialist III
‎2023-03-31 08:06 AM

Hi @SK28 

Try this code:

Table1:
LOAD * INLINE [
PARENT ID, TYPE, CHILD ID, status, COLUMN2, COLUMN3
123, ABC, 123-V3, done, ab, sd
, , 123-V4, , ,
, , 123-V5, , ,
, , 123-V6, , ,
, , 123-V7, , ,
234, bcf, 234-v1-15, complete, ef, ab
, , 234-v1-16, , ,
, , 234-v1-17, , ,
456, def, 456v1-123-v6-12, WIP, gh, kl
, , 456v1-123-v6-13, , ,
, , 456v1-123-v6-14, , ,
789, ght, ghtv1-d, complete, jk, ol
910, ftr, 910-fgt, complete, gh, kl
];

Table2:
NOCONCATENATE
LOAD *,
IF(Len(Trim(status))>0, status, Peek(status_New)) as status_New,
IF(Len(Trim(COLUMN2))>0, COLUMN2, Peek(COLUMN2_New)) as COLUMN2_New,
IF(Len(Trim(COLUMN3))>0, COLUMN3, Peek(COLUMN3_New)) as COLUMN3_New
RESIDENT Table1 Order By [PARENT ID], [CHILD ID] ASC;
DROP TABLE Table1;

Or other solution,

Table1:
LOAD *, RecNo() AS RecNo INLINE [
PARENT ID, TYPE, CHILD ID, status, COLUMN2, COLUMN3
123, ABC, 123-V3, done, ab, sd
, , 123-V4, , ,
, , 123-V5, , ,
, , 123-V6, , ,
, , 123-V7, , ,
234, bcf, 234-v1-15, complete, ef, ab
, , 234-v1-16, , ,
, , 234-v1-17, , ,
456, def, 456v1-123-v6-12, WIP, gh, kl
, , 456v1-123-v6-13, , ,
, , 456v1-123-v6-14, , ,
789, ght, ghtv1-d, complete, jk, ol
910, ftr, 910-fgt, complete, gh, kl
];

Table2:
NOCONCATENATE
LOAD *,
IF(Len(Trim(status))>0, status, Peek(status_New)) as status_New,
IF(Len(Trim(COLUMN2))>0, COLUMN2, Peek(COLUMN2_New)) as COLUMN2_New,
IF(Len(Trim(COLUMN3))>0, COLUMN3, Peek(COLUMN3_New)) as COLUMN3_New
RESIDENT Table1 Order By RecNo ASC;
DROP TABLE Table1;

 

aveeeeeee7en_0-1680273127338.png

Regards,

Av7eN

640 Views
0 Likes
Gabbar
Specialist
Specialist
‎2023-03-31 08:21 AM

I think peek Should Work,
I hope your data is in correct Order
Try this:
Load 
Coalesce(Column,peek(Column)) as Column 
From Source;

(Repeat it for every column)

635 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2023-03-31 09:04 AM

@SK28  to fill your data, sorting of the data is important. You need to sort your data as presented in your data example. Hence, you need to take resident load to use order by columns. I have used Child id and parent id in order by according your sample data to sort it, but you need to find actual columns which can sort your data correctly

 

Data:
Load * Inline [
PARENT ID, TYPE, CHILD ID, status, COLUMN2, COLUMN3
123, ABC, 123-V3, done, ab, sd
, , 123-V4, , ,
, , 123-V5, , ,
, , 123-V6, , ,
, , 123-V7, , ,
234, bcf, 234-v1-15, complete, ef, ab
, , 234-v1-16, , ,
, , 234-v1-17, , ,
456, def, 456v1-123-v6-12, WIP, gh, kl
, , 456v1-123-v6-13, , ,
, , 456v1-123-v6-14, , ,
789, ght, ghtv1-d, complete, jk, ol
910, ftr, 910-fgt, complete, gh, kl
];

Final:
NoConcatenate
Load if(len(trim([PARENT ID]))=0,Peek('PARENT ID'),[PARENT ID]) as [PARENT ID],
     if(len(trim(TYPE))=0,Peek('TYPE'),TYPE) as TYPE,
     if(len(trim(COLUMN2))=0,Peek('COLUMN2'),COLUMN2) as COLUMN2,
     if(len(trim(COLUMN3))=0,Peek('COLUMN3'),COLUMN3) as COLUMN3,
     if(len(trim(status))=0,Peek('status'),status) as status,
     [CHILD ID]
Resident Data
Order by [CHILD ID],[PARENT ID];

Drop Table Data;
624 Views
0 Likes
sidhiq91
Specialist II
Specialist II
‎2023-03-31 09:19 AM

@SK28  Please see the code below used in the Script Editor:

NoConcatenate
Temp:
Load
PARENTID,
TYPE,
CHILDID,
status,
COLUM2,
COLUMN3

Inline [
PARENTID, TYPE, CHILDID, status, COLUM2, COLUMN3
123, ABC, 123-V3, done, ab, sd
, ,123-V4,,,
, ,123-V5,,,
, ,123-V6,,,
, ,123-V7,, ,
234, bcf, 234-v1-15, complete, ef, ab
, ,234-v1-16, , ,
, ,234-v1-17, ,,
456, def, 456v1-123-v6-12, WIP, gh, kl
, ,456v1-123-v6-13, , ,
, ,456v1-123-v6-14, , ,
789, ght, ghtv1-d, complete, jk, ol
910, ftr, 910-fgt, complete, gh, kl

];

NoConcatenate
Temp1:
Load
if(not isnull(EmptyIsNull(PARENTID)),PARENTID,peek(New_PARENTID)) as New_PARENTID,
if(not isnull(EmptyIsNull(TYPE)),TYPE,peek(New_TYPE)) as New_TYPE,
if(not isnull(EmptyIsNull(status)),status,peek(New_status)) as New_status,
if(not isnull(EmptyIsNull(COLUM2)),COLUM2,peek(New_COLUM2)) as New_COLUM2,
if(not isnull(EmptyIsNull(COLUMN3)),COLUMN3,peek(New_COLUMN3)) as New_COLUMN3

Resident Temp;

Drop table Temp;

Exit Script;

sidhiq91_0-1680268768036.png

 

620 Views
0 Likes
SK28
Creator
Creator
‎2023-03-31 10:45 AM
Author

In response to aveeeeeee7en

Hi @aveeeeeee7en it's working only for 3 records, if they blank records are more than 3, it's showing as '-' Null() blank,

else 

Can we achieve this I have child column like this

child
ABC-1013.V2-24187
ABC-1013.V2
ABC-1046.V2-24428
ABC-1046.V2
ABC-1178.V2-25465
ABC-1178.V2
ABC-1229.V2-25925
ABC-1229.V2
ABC-1250.V2-26366
ABC-1250.V2
ABC-1323.V2-27096
ABC-1323.V2
ABC-1365.V2-24243
ABC-1365.V2
ABC-1597.V2-19167
ABC-1597.V2
ABC-1631.V2-25265
ABC-1631.V2
ABC-1701.V2-21053

 

Output should be:

parent
ABC-1013.V2
ABC-1013.V2
ABC-1046.V2
ABC-1046.V2
ABC-1178.V2
ABC-1178.V2
ABC-1229.V2
ABC-1229.V2
ABC-1250.V2
ABC-1250.V2
ABC-1323.V2
ABC-1323.V2
ABC-1365.V2
ABC-1365.V2
ABC-1597.V2
ABC-1597.V2
ABC-1631.V2
ABC-1631.V2
612 Views
0 Likes
aveeeeeee7en
Specialist III
Specialist III
‎2023-03-31 04:24 PM

In response to SK28

Hi @SK28, It's unclear from your last reply about the issue.

Please share your sample data or snapshot where you're saying that records are coming as blank (null) wherever records are more than 3.

Can you please use below snapshot and let us know  where exactly you're facing the issue.

aveeeeeee7en_0-1680294158065.png

Regards,

Av7eN

592 Views
0 Likes