topic Re: Load only first record in Connectivity & Data Prep

Load only first record

hhajjali — Wed, 31 Jan 2018 08:56:58 GMT

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.

Re: Load only first record

kaushiknsolanki — Wed, 31 Jan 2018 09:12:31 GMT

Hi,

Check this link.

Using FirstSortedValue in the Script

Regards,

Kaushik Solanki

Re: Load only first record

ali_hijazi — Wed, 31 Jan 2018 09:24:27 GMT

First 1 Load *

is this what you mean?

Re: Load only first record

hhajjali — Fri, 02 Feb 2018 21:32:57 GMT

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];