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: 
rajeshqvd
Creator II
Creator II

Script creating new field

IDSubidAreaNewPeekRequired Output
1111   
2221   
3331   
3332INDIND/IND
3333PAKPAK/INDPAK/IND
3334 /PAKPAK/IND

Please help on this need to create new field like "Required Output" .

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:
LOAD *,
	 If(ID = Previous(ID),
	 	If(Len(Trim(Area)) > 0,
	 		If(Len(Trim(Peek('Output'))) > 0,
	 			Peek('Output') & '/' & Area,
	 			Area),
	 		Peek('Output')),
	 	Area) as Output;
LOAD * INLINE [
    ID, Subid, Area
    111, 1,  
    222, 1,  
    333, 1,  
    333, 2, IND
    333, 3, PAK
    333, 4,  
];

View solution in original post

9 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III

Hi, can you explain the logic you need please?

rajeshqvd
Creator II
Creator II
Author

Thanks for quick rply....First 3 columns are data 5th one is the required output I am getting 4th one.
sunny_talwar

Try this

Table:
LOAD *,
	 If(ID = Previous(ID),
	 	If(Len(Trim(Area)) > 0,
	 		If(Len(Trim(Peek('Output'))) > 0,
	 			Peek('Output') & '/' & Area,
	 			Area),
	 		Peek('Output')),
	 	Area) as Output;
LOAD * INLINE [
    ID, Subid, Area
    111, 1,  
    222, 1,  
    333, 1,  
    333, 2, IND
    333, 3, PAK
    333, 4,  
];
rajeshqvd
Creator II
Creator II
Author

Wow your the best
rajeshqvd
Creator II
Creator II
Author

IDSubidAreaNewPeekRequired Output
1111   
2221 IND IND
3332INDIND/PAK/IND
3333PAKPAK/INDPAK/IND
3334 /PAKPAK/IND

small change need out put like this....... thanks

sunny_talwar

What is the Logic here? and can you explain what the output would look if the data was like this

IDSubidAreaNewPeekRequired Output
1111   
2221 IND IND
3332INDIND/PAK/IND
3333PAKPAK/INDPAK/IND
3334 /PAKPAK/IND
3335AUS ?

 

Will it be AUS/PAK/IND or PAK/IND/AUS or PAK/AUS/IND? Again what is the logic behind the output?

rajeshqvd
Creator II
Creator II
Author

if it has n number of values need output like n/n/n/n
if it AUS then output AUS/PAK/IND
sunny_talwar

Try this

Table:
LOAD *,
	 If(ID = Previous(ID),
	 	If(Len(Trim(Area)) > 0,
	 		If(Len(Trim(Peek('Output'))) > 0,
	 			Area & '/' & Peek('Output'),
	 			Area),
	 		Peek('Output')),
	 	Area) as Output;
LOAD * INLINE [
    ID, Subid, Area
    111, 1,  
    222, 1,  
    333, 1,  
    333, 2, IND
    333, 3, PAK
    333, 4,  
    333, 5, AUS
];
rajeshqvd
Creator II
Creator II
Author

sorry for giving trouble need below output
ID Subid Area Output
111 1
222 1
333 1
333 2 IND AUS/PAK/IND
333 3 PAK AUS/PAK/IND
333 4 AUS/PAK/IND
333 5 AUS AUS/PAK/IND