Assigning values to other colums

[deepakkrish]

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.

[Taoufiq_Zarra]

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

[deepakkrish]

Hi,

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:

[Saravanan_Desingh · Accepted solution]

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]

It's working . Got expected output.

Thank you @Saravanan_Desingh