Hi to Achieve column ID

alvinford — Wed, 04 Jul 2018 11:27:05 GMT

Hi All,

I have the following data i.e. Call , Number and Name.

Based on the combination of Number and Name, need to derive the ID column.

The Number may have different names associated with it.

if the Number has only one Name the ID should be 1.

if the Number has more than one name the ID should be incremented. Example as highlighted in Blue below.

Please add your suggestions to achieve this.

Class	Number	Name	ID
1	801920170706	Sarinya	1
1	801920170706	Sarinya	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	840520170607	Yuphaporn	1
1	850620170918	ANGSAYA	1
1	850620170918	CHALUAY	2
1	850620170918	CHALUAY	2
1	850620170918	WEERA	3
1	850620170918	WEERA	3
1	854820170815	Inta	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873720170810	Sritutti	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Alisa	1
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Hannah	2
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873820170702	Paul	3
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Gary	1
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2
1	873920170708	Sreeprasarn	2

Re: Hi to Achieve column ID

ogautier62 — Wed, 04 Jul 2018 12:10:42 GMT

Hi,

make a sheet with Number, Name in dimension

and ID will be given by rowno()

regards

Re: Hi to Achieve column ID

alvinford — Wed, 04 Jul 2018 12:59:52 GMT

Hi Olivier,

Thank You for your reply. I works fine in the front end.

Want to achieve this in the script. Sorry didn't mention this in my post.

Your help is appreciated.

Thanks You,

Alvin

Re: Hi to Achieve column ID

ogautier62 — Wed, 04 Jul 2018 14:02:54 GMT

test:

LOAD Number,Name FROM 'your file' ;

load *, if(peek('Number') <> Number,1, if(peek('Name') <> Name,peek('rang')+1,peek('rang'))) as ID resident test order by Number,Name;

regards

Re: Hi to Achieve column ID

alvinford — Wed, 04 Jul 2018 14:51:02 GMT

Hi Olivier,

It works perfectly.. thank You ...

Appreciate your help ..

will mark it as correct answer ..

Re: Hi to Achieve column ID

MarcoWedel — Sun, 08 Jul 2018 23:10:18 GMT

Hi,

another solution might be:

LOAD Autonumber(Name, Number) as ID
FROM ...

hope this helps

regards

Marco

topic Re: Hi to Achieve column ID in QlikView

Hi to Achieve column ID

Re: Hi to Achieve column ID

Re: Hi to Achieve column ID

Re: Hi to Achieve column ID

Re: Hi to Achieve column ID

Re: Hi to Achieve column ID