How to get first record of every dataset in the table

[ashmitp869]

Hi All,

How to pick the first record of  each set in qlikview script.

I am using the script  , so when the middle part is joining marked bold I am getting duplicate values but I only want the first record of each set ...Please kindly help 

SE_TMP:
Load
[Facility Identifier],
"Derived National Service Event Record Id",
"SE.Service Event Record ID",
1 as count_se
1 as count_oos
FROM
QVD;

SE:
NoConcatenate
LOAD DISTINCT
"OOS.Derived National Service Event Record Id" as IDKey,
[Facility Identifier],
"SE.Service Event Record ID",
1 as count_se
0 as count_oos
RESIDENT OOS
WHERE NOT EXISTS ("Derived National Service Event Record Id","OOS.Derived National Service Event Record Id")
;

Concatenate(SE)
LOAD
"Derived National Service Event Record Id" as IDKey,
"Facility Identifier"
,"SE.Service Event Record ID"
,count_se as SE.count_se
,count_moh_se as SE.count_moh_se
RESIDENT SE_TMP;

DROP TABLE SE_TMP;

[njmaehler]

Hi,
Firstly, what order are you reading your records in? Perhaps you need to have an ID or date but at least an order by clause to ensure that you are always getting the correct "first" record. 
Secondly, can you change the column headers to be what you are writing in your script because this will help understand which columns you are trying to join on. 
I can see that you are trying to join two datasets together but only "unique" records so that you don't get the duplicates (hence the not exists).

Without a full understanding of your requirements and the matching of the script to the table image provided I cannot give you your exact code that you need hence I have attached a little example where you can get a "Order" starting at one for each combination of ID1 and ID2. See if that helps and then you can add a where clause on where Order = 1 and use that record in your join. 
i.e. you can get the order on your ID (column A) and  the service record id (Column C).