How create "index" for field?

[vchuprina]

Hi All,

I have a list of countries and need to join them with Countrylist(second table). I use following code to generate  Countrylist

LET i=1;

Do while i<=$(MaxCountry)

VirtualFields:
LOAD
$(i)&' CoO' as [CountryList]
AutoGenerate(1);

LET i=i+1;

Loop; 

How can I join them? For example PL should be 1 CoO, US - 2 CoO.

How can I add 'index' - key of County

Country	Sales
PL	16059
US	9025
CN	5589
BE	277
MX	78
CA	30
GB	7
TW	5
DE	3

CountryList

1 CoO

2 CoO

3 CoO

4 CoO

5 CoO

6 CoO

7 CoO

8 CoO

9 CoO

10 CoO

11 CoO

12 CoO

13 CoO

14 CoO

15 CoO

16 CoO

17 CoO

18 CoO

[sunny_talwar]

May be try something like this:

Table:

LOAD *,

  AutoNumber(Country) & 'CoO' as CountryList;

LOAD * INLINE [

    Country,    Sales

    PL,    16059

    US,    9025

    CN,    5589

    BE,    277

    MX,    78

    CA,    30

    GB,    7

    TW,    5

    DE,    3

];

[MarcoWedel]

or

RecNo()&' CoO' as CountryList

hope this helps

regards

Marco

[vchuprina]

Hi Marco,

Currently I have following table:

TestCountry	Position
7307191090-1	427
7307191090-3	426
7307191090-5	425
7307191090-7	424
7307191090-30	423
7307191090-78	422
7307191090-277	421
7307191090-5589	420
7307191090-9025	419
7307191090-16059	418
8424890000-3	268
8424890000-3	267
8424890000-2254	266
8424890000-7236	265

I want get following

TestCountry	Position	Number
7307191090-1	427	10
7307191090-3	426	9
7307191090-5	425	8
7307191090-7	424	7
7307191090-30	423	6
7307191090-78	422	5
7307191090-277	421	4
7307191090-5589	420	3
7307191090-9025	419	2
7307191090-16059	418	1
8424890000-3	268	4
8424890000-3	267	3
8424890000-2254	266	2
8424890000-7236	265	1

[Gysbert_Wassenaar]

Temp:

LOAD

     TestCountry,

     Position

FROM

     ...somewhere...

     ;

Result:

LOAD

     *, RecNo() as Number

RESIDENT

     Temp

ORDER BY

     Position

     ;

DROP TABLE Temp;

[vchuprina]

Hi Gysbert,

In this case I got following result :

TestCountry	Position	Number
7307191090-1	427	427
7307191090-3	426	426
7307191090-5	425	425
7307191090-7	424	424
7307191090-30	423	423
7307191090-78	422	422
7307191090-277	421	421
7307191090-5589	420	420
7307191090-9025	419	419
7307191090-16059	418	418
8424890000-3	268	268
8424890000-3	267	267
8424890000-2254	266	266
8424890000-7236	265	265

[Gysbert_Wassenaar · Accepted solution]

Wow, that's amazing. You're breaking laws of nature! Cool!. I can't reproduce that kind of magic.

But I see my solution is not exactly what you asked for. Try this:

Temp:

LOAD * INLINE [

    TestCountry, Position

    7307191090-1, 427

    7307191090-3, 426

    7307191090-5, 425

    7307191090-7, 424

    7307191090-30, 423

    7307191090-78, 422

    7307191090-277, 421

    7307191090-5589, 420

    7307191090-9025, 419

    7307191090-16059, 418

    8424890000-3, 268

    8424890000-3, 267

    8424890000-2254, 266

    8424890000-7236, 265

];

Result:

LOAD

  *,

  AutoNumber(Position,SubField(TestCountry,'-',1)) as Number

Resident

  Temp

Order By

  Position

  ;

DROP TABLE Temp;