Script creating new field

[rajeshqvd]

ID	Subid	Area	NewPeek	Required Output
111	1
222	1
333	1
333	2	IND	IND/	IND
333	3	PAK	PAK/IND	PAK/IND
333	4		/PAK	PAK/IND

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

[hopkinsc]

Hi, can you explain the logic you need please?

[rajeshqvd]

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

[sunny_talwar · Accepted solution]

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]

Wow your the best

[rajeshqvd]

ID	Subid	Area	NewPeek	Required Output
111	1
222	1	IND		IND
333	2	IND	IND/	PAK/IND
333	3	PAK	PAK/IND	PAK/IND
333	4		/PAK	PAK/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

ID	Subid	Area	NewPeek	Required Output
111	1
222	1	IND		IND
333	2	IND	IND/	PAK/IND
333	3	PAK	PAK/IND	PAK/IND
333	4		/PAK	PAK/IND
333	5	AUS		?

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

[rajeshqvd]

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]

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