How to load MAX version of Id

[DipeshVadgama]

Hi,

I have a data with Id which is combine with version. I want to load only last version of ids.

Example Data

ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3

Result

A1234_1

C5678_2

D9999_3

[Channa]

load where ID like '%_%'

if you want to load only MAX ID try to use SUBField and get only Max(Number)

[OmarBenSalem]

t0:
load ID, subfield(ID,'_',1) as First,subfield(ID,'_',2) as second Inline [
ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3
];

NoConcatenate
final:
load First as ID resident t0 where len(trim(second))=0;
Concatenate(final)
load First&'_'&max(second) as ID Resident t0 group by First;

drop table t0;

result:

[pradosh_thakur]

load ID & '_' &  MAX(MAX_NUM) as ID
group by ID;
load subfield(ID,'_',2) AS MAX_NUM ,subfield(ID,'_',1) AS ID inline  [
ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3
];

[Gysbert_Wassenaar]

This should do the trick:

LOAD
    FirstSortedValue(ID, -Version) as ID
GROUP BY
    BaseID;
LOAD
    ID, 
    SubField(ID,'_',1) as BaseID, 
    Alt(SubField(ID,'_',2),0) as Version
FROM 
    ...source...
;

[sunny_talwar]

Another option

Table:
LOAD SubField(ID & '_', '_', 1) as IDTemp,
	 MaxString(ID) as ID
Group By  SubField(ID & '_', '_', 1);
LOAD * INLINE [
    ID
    A1234
    A1234_1
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

DROP Field IDTemp;

[DipeshVadgama]

Hi All,

I have applied your scripts which do works. But when I apply same script on Hugh file it take a lot of time to get load.

It had a lot of other fields, Any suggestion.

[sunny_talwar · Accepted solution]

Here is another option, but require you to create a qvd layer

TempTable:
LOAD * INLINE [
    ID
    A1234
    A1234_1
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

Table:
LOAD ID,
	 SubField(ID & '_', '_', 1) as IDTemp
Resident TempTable
Order By ID desc;

DROP Table TempTable;

STORE Table into Table.qvd (qvd);
DROP Table Table;

FinalTable:
LOAD ID,
	 IDTemp
FROM Table.qvd (qvd)
Where not Exists(IDTemp);

[DipeshVadgama]

Hi, Thank you it work. But just one last thing. Currently it load max _numbers, but if any ID do not have _ it also has to added.

[sunny_talwar]

What exactly do you mean? Do you mean if your data was like this

TempTable:
LOAD * INLINE [
    ID
    A1234
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

You would still want A1234 to load as the max value? My script seems to do that already.