

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Load only first record
Hello,
I have a table which I load in via the following script:
[versions]:
LOAD
[id_u3] AS [id_u3],
[name_u2] AS [name_u2],
[__FK_versions] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_versions]) ORDER BY [id_u3];
Each [id_u3] have only one [name_u2] but [__KEY_fields] can have multiple reccord of couple ( [id_u3] , [name_u2]):
Example
[__KEY_fields] | id_u3 | name_u2 |
---|---|---|
1 | 1 | TEST1 |
1 | 2 | TEST2 |
1 | 3 | TEST3 |
1 | 4 | TEST4 |
What I realle need to load in the application for each unique [__KEY_fields] is only the couple ( [id_u3] , [name_u2]) where [id_u3] is the smallest value.
I tried to create a new table in the load script as follow; but it is in error and impossible to launch data load:
[versions1]:
LOAD
FirstSortedValue(id_u3, -Aggr(id_u3, name_u2,[__FK_versions])) as [id_u3],
FirstSortedValue(id_u3, -Aggr(id_u3, name_u2,[__FK_versions])) as [name_u2],
[__FK_versions] AS [__KEY_fields]
RESIDENT [version];
Any ideas ?
Thanks for your help.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for replies,
First 1 Load * will only load the first record, it is not really what I need, because I want the first record for each [__KEY_FIELDS].
The solution is using firstsortedvalue, Kaushik's response helped me to figure out how to resolve my problem:
First I order my table by [__KEY_fields],[id_u7] :
[versions]:
LOAD
[id_u7] AS [IDVersion],
[name_u4] AS [TEMPRELEASE],
[__FK_versions] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_versions])
order by [__KEY_fields],[id_u7];
Then I created another table using fisrtsorted value:
[Release]:
Load
FirstSortedValue([TEMPRELEASE],[IDVersion]) As ARELEASE,
[__KEY_fields]
RESIDENT [versions]
Group by [__KEY_fields];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Check this link.
Using FirstSortedValue in the Script
Regards,
Kaushik Solanki


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First 1 Load *
is this what you mean?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for replies,
First 1 Load * will only load the first record, it is not really what I need, because I want the first record for each [__KEY_FIELDS].
The solution is using firstsortedvalue, Kaushik's response helped me to figure out how to resolve my problem:
First I order my table by [__KEY_fields],[id_u7] :
[versions]:
LOAD
[id_u7] AS [IDVersion],
[name_u4] AS [TEMPRELEASE],
[__FK_versions] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_versions])
order by [__KEY_fields],[id_u7];
Then I created another table using fisrtsorted value:
[Release]:
Load
FirstSortedValue([TEMPRELEASE],[IDVersion]) As ARELEASE,
[__KEY_fields]
RESIDENT [versions]
Group by [__KEY_fields];
