Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
load where ID like '%_%'
if you want to load only MAX ID try to use SUBField and get only Max(Number)
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:
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 ];
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... ;
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;
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.
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);
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.
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.