topic Re: Script creating new field in QlikView

Script creating new field

rajeshqvd — Sat, 16 Nov 2024 05:05:40 GMT

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" .

Re: Script creating new field

hopkinsc — Fri, 30 Nov 2018 14:05:11 GMT

Hi, can you explain the logic you need please?

Re: Script creating new field

rajeshqvd — Fri, 30 Nov 2018 14:14:01 GMT

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

Re: Script creating new field

sunny_talwar — Fri, 30 Nov 2018 14:23:36 GMT

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,  
];

Re: Script creating new field

rajeshqvd — Fri, 30 Nov 2018 15:02:19 GMT

Wow your the best

Re: Script creating new field

rajeshqvd — Fri, 30 Nov 2018 15:16:04 GMT

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

Re: Script creating new field

sunny_talwar — Fri, 30 Nov 2018 15:19:53 GMT

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?

Re: Script creating new field

rajeshqvd — Fri, 30 Nov 2018 15:22:45 GMT

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

Re: Script creating new field

sunny_talwar — Fri, 30 Nov 2018 15:29:05 GMT

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
];

Re: Script creating new field

rajeshqvd — Fri, 30 Nov 2018 15:32:36 GMT

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