Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
YoussefBelloum
Champion
Champion

Replace NA value with next column value

Hello everyone,

hope you're doing well !

please find below my datasctructure for today's problem:

colonne1colonne2colonne3colonne4colonne5colonne6colonne7
NAligne1.colonne2ligne1.colonne3ligne1.colonne4ligne1.colonne5ligne1.colonne6ligne1.colonne7
ligne2.colonne1NAligne2.colonne3ligne2.colonne4ligne2.colonne5ligne2.colonne6ligne2.colonne7
ligne3.colonne1ligne3.colonne2NAligne3.colonne4ligne3.colonne5ligne3.colonne6ligne3.colonne7
ligne4.colonne1ligne4.colonne2ligne4.colonne3NAligne4.colonne5ligne4.colonne6ligne4.colonne7
ligne5.colonne1ligne5.colonne2ligne5.colonne3ligne5.colonne4NAligne5.colonne6ligne5.colonne7
ligne6.colonne1ligne6.colonne2ligne6.colonne3ligne6.colonne4ligne6.colonne5NAligne6.colonne7
ligne7.colonne1ligne7.colonne2NANAligne7.colonne5ligne7.colonne6ligne7.colonne7

 

For every single line, I need to replace every NA value with next column value, like the result below:

colonne1colonne2colonne3colonne4colonne5colonne6colonne7
ligne1.colonne2ligne1.colonne3ligne1.colonne4ligne1.colonne5ligne1.colonne6ligne1.colonne7 
ligne2.colonne1ligne2.colonne3ligne2.colonne4ligne2.colonne5ligne2.colonne6ligne2.colonne7 
ligne3.colonne1ligne3.colonne2ligne3.colonne4ligne3.colonne5ligne3.colonne6ligne3.colonne7 
ligne4.colonne1ligne4.colonne2ligne4.colonne3ligne4.colonne5ligne4.colonne6ligne4.colonne7 
ligne5.colonne1ligne5.colonne2ligne5.colonne3ligne5.colonne4ligne5.colonne6ligne5.colonne7 
ligne6.colonne1ligne6.colonne2ligne6.colonne3ligne6.colonne4ligne6.colonne5ligne6.colonne7 
ligne7.colonne1ligne7.colonne2ligne7.colonne5ligne7.colonne6ligne7.colonne7  

 

like you noticed on the line 7, there is multiple NA values, the idea is to replace them all with next columns values, and all cases are possible (only one NA, multiple NA side by side, multiple NA separated by columns values, etc...)

One solution consists of using A LOT of nested IF statements (A nightmare to write and maintain) so I'm searching for a slightly more efficient solution..

Thank you for your help and have a nice weekend 🙂

Youssef B

 

 

Labels (2)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

I used multiple preload steps to verify that it worked.

 

Data:
Load SubField(str3,',',1) as colonne1,
SubField(str3,',',2) as colonne2,
SubField(str3,',',3) as colonne3,
SubField(str3,',',4) as colonne4,
SubField(str3,',',5) as colonne5,
SubField(str3,',',6) as colonne6,
SubField(str3,',',7) as colonne7;
Load *,
str2&Repeat(',',7-FieldCount-1) as str3;
load *,
SubStringCount(str2,',') as FieldCount;
Load *,
replace(str1,'NA,','') as str2;
Load colonne1&','&colonne2&','&colonne3&','&colonne4&','&colonne5&','&colonne6&','&colonne7 as str1;
Load * Inline [
colonne1,colonne2,colonne3,colonne4,colonne5,colonne6,colonne7
NA,ligne1.colonne2,ligne1.colonne3,ligne1.colonne4,ligne1.colonne5,ligne1.colonne6,ligne1.colonne7
ligne2.colonne1,NA,ligne2.colonne3,ligne2.colonne4,ligne2.colonne5,ligne2.colonne6,ligne2.colonne7
ligne3.colonne1,ligne3.colonne2,NA,ligne3.colonne4,ligne3.colonne5,ligne3.colonne6,ligne3.colonne7
ligne4.colonne1,ligne4.colonne2,ligne4.colonne3,NA,ligne4.colonne5,ligne4.colonne6,ligne4.colonne7
ligne5.colonne1,ligne5.colonne2,ligne5.colonne3,ligne5.colonne4,NA,ligne5.colonne6,ligne5.colonne7
ligne6.colonne1,ligne6.colonne2,ligne6.colonne3,ligne6.colonne4,ligne6.colonne5,NA,ligne6.colonne7
ligne7.colonne1,ligne7.colonne2,NA,NA,ligne7.colonne5,ligne7.colonne6,ligne7.colonne7
];

View solution in original post

2 Replies
jwjackso
Specialist III
Specialist III

I used multiple preload steps to verify that it worked.

 

Data:
Load SubField(str3,',',1) as colonne1,
SubField(str3,',',2) as colonne2,
SubField(str3,',',3) as colonne3,
SubField(str3,',',4) as colonne4,
SubField(str3,',',5) as colonne5,
SubField(str3,',',6) as colonne6,
SubField(str3,',',7) as colonne7;
Load *,
str2&Repeat(',',7-FieldCount-1) as str3;
load *,
SubStringCount(str2,',') as FieldCount;
Load *,
replace(str1,'NA,','') as str2;
Load colonne1&','&colonne2&','&colonne3&','&colonne4&','&colonne5&','&colonne6&','&colonne7 as str1;
Load * Inline [
colonne1,colonne2,colonne3,colonne4,colonne5,colonne6,colonne7
NA,ligne1.colonne2,ligne1.colonne3,ligne1.colonne4,ligne1.colonne5,ligne1.colonne6,ligne1.colonne7
ligne2.colonne1,NA,ligne2.colonne3,ligne2.colonne4,ligne2.colonne5,ligne2.colonne6,ligne2.colonne7
ligne3.colonne1,ligne3.colonne2,NA,ligne3.colonne4,ligne3.colonne5,ligne3.colonne6,ligne3.colonne7
ligne4.colonne1,ligne4.colonne2,ligne4.colonne3,NA,ligne4.colonne5,ligne4.colonne6,ligne4.colonne7
ligne5.colonne1,ligne5.colonne2,ligne5.colonne3,ligne5.colonne4,NA,ligne5.colonne6,ligne5.colonne7
ligne6.colonne1,ligne6.colonne2,ligne6.colonne3,ligne6.colonne4,ligne6.colonne5,NA,ligne6.colonne7
ligne7.colonne1,ligne7.colonne2,NA,NA,ligne7.colonne5,ligne7.colonne6,ligne7.colonne7
];

YoussefBelloum
Champion
Champion
Author

@jwjackso 

It's awesome !

Thank you for your time

 

Youssef B