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: 
deepakkrish
Creator
Creator

Assigning values to other colums

Hi All,

My requirement is,  There are level of columns in my table.

If the 4th level column is null then we have to assign the values in below order. 

[4] = [3], [3] = [2], [2] = [1], [1] = ''  " WHERE [4] = '' "

How to create this in QlikView load script.

 

Thanks in Advance,

Deepak.

1 Solution

Accepted Solutions
Saravanan_Desingh

Try this update version,

tab1:
LOAD * INLINE [
    Name, L1, L2, L3, L4
    A, 100, 90, 80, 70
    B, 90, 80, 70, 
    C, 80, 70, , 
    D, 70, , , 
];

Left Join(tab1)
LOAD Name, If(Len(Trim(P1))=0, '0',P1) As Pat;
LOAD Name, If(Len(Trim(L1))=0,'L1')&If(Len(Trim(L2))=0,'L2')&
			If(Len(Trim(L3))=0,'L3')&If(Len(Trim(L4))=0,'L4') As P1
Resident tab1;

tab2:
NoConcatenate
LOAD Name,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L1, Null(), Null(), Null()) As L1,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L2, L1, Null(), Null()) As L2,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L3, L2, L1, Null()) As L3,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L4, L3, L2, L1) As L4
Resident tab1;

Drop Table tab1;	

View solution in original post

6 Replies
Taoufiq_Zarra

can you elaborate or share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
deepakkrish
Creator
Creator
Author

Hi,

 

deepakkrish_0-1629367566011.png

 

This is my expected output.

The table should be flattening like this way.  If the level 4 is an empty then it should be filled in this way.

Thanks,

Deepak.

Saravanan_Desingh

Try this, I am here assuming that, we have hardcoded Pattern (Pat). This part can also be automated if u want.

tab1:
LOAD * INLINE [
    Name, L1, L2, L3, L4
    A, 100, 90, 80, 70
    B, 90, 80, 70, 
    C, 80, 70, , 
    D, 70, , , 
];

Left Join(tab1)
LOAD * INLINE [
    Name, Pat
    A, 0
    B, L4
    C, L3L4
    D, L2L3L4
];

tab2:
NoConcatenate
LOAD Name,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L1, Null(), Null(), Null()) As L1,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L2, L1, Null(), Null()) As L2,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L3, L2, L1, Null()) As L3,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L4, L3, L2, L1) As L4
Resident tab1;

Drop Table tab1;	
Saravanan_Desingh

Output:

commQV87.PNG

Saravanan_Desingh

Try this update version,

tab1:
LOAD * INLINE [
    Name, L1, L2, L3, L4
    A, 100, 90, 80, 70
    B, 90, 80, 70, 
    C, 80, 70, , 
    D, 70, , , 
];

Left Join(tab1)
LOAD Name, If(Len(Trim(P1))=0, '0',P1) As Pat;
LOAD Name, If(Len(Trim(L1))=0,'L1')&If(Len(Trim(L2))=0,'L2')&
			If(Len(Trim(L3))=0,'L3')&If(Len(Trim(L4))=0,'L4') As P1
Resident tab1;

tab2:
NoConcatenate
LOAD Name,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L1, Null(), Null(), Null()) As L1,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L2, L1, Null(), Null()) As L2,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L3, L2, L1, Null()) As L3,
	Pick(Match(Pat,'0','L4','L3L4','L2L3L4'),L4, L3, L2, L1) As L4
Resident tab1;

Drop Table tab1;	
deepakkrish
Creator
Creator
Author

It's working . Got expected output.

 

Thank you @Saravanan_Desingh