topic Re: First Record required from the data based on criteria in App Development

First Record required from the data based on criteria

P_Kale — Wed, 07 Jan 2026 07:50:05 GMT

Hello,

I want to extract only 1 record from the data. In ABC data there are more than 1 records against these agent codes and i want to filter only 1 record from that. I have written below given code but i have not got any records.

Any help is appreciated.

TEST:

Load * Inline [
AGENT_CODE
12345678
23456789
34568901
];

Left Join
LOAD
POLICY_NO,
AREA_CODE,
APPLICATION_NO,
text(AGENT_CODE)
FROM [lib://SQL_BASE_EXTRACT_LAYER/LAYER_1/ABC.qvd]
(qvd) where Exists(AGENT_CODE);

NoConcatenate

FirstRecordPerAgent:
LOAD
AGENT_CODE,
FirstSortedValue(POLICY_NO, POLICY_NO) AS First_POLICY_NO,
FirstSortedValue(AREA_CODE, POLICY_NO) AS First_AREA_CODE,
FirstSortedValue(APPLICATION_NO, POLICY_NO) AS First_APPLICATION_NO
RESIDENT TEST
GROUP BY AGENT_CODE;
Drop Table TEST;

Re: First Record required from the data based on criteria

RafaelBarrios — Wed, 07 Jan 2026 12:49:50 GMT

Hello @P_Kale

try this

TEST: Load * inline [ AGENT_CODE 12345678 23456789 34568901 ]; lEFT JOIN(TEST) //precedent load to filter the unwanted records load * where record_flag = 1; //precedent load to flag the record we need Load if(AGENT_CODE <> previous(AGENT_CODE),1,0) as record_flag, *; //LETS SAY THIS IS YOUR QVD Load * inline [ AGENT_CODE,DATE,AMOUNT 12345678,2025-01-01,10 12345678,2025-01-03,20 12345678,2025-01-02,30 23456789,2025-01-06,40 23456789,2025-01-02,50 23456789,2025-01-03,60 23456789,2025-01-05,70 34568901,2025-01-02,80 34568901,2025-01-01,90 34568901,2025-01-02,100 34568901,2025-01-03,110 34568901,2025-01-04,120 34568901,2025-01-05,130 34568901,2025-01-06,140 ];

in my case, the "qvd" is sorted by AGENT_CODE, maybe you could need to first sort your Qvd and then do the flagging when AGENT_CODE change

TEST: Load * inline [ AGENT_CODE 12345678 23456789 34568901 ]; //LETS SAY THIS IS YOUR QVD TEST2: Load * inline [ AGENT_CODE,DATE,AMOUNT 12345678,2025-01-01,10 12345678,2025-01-02,30 34568901,2025-01-05,130 23456789,2025-01-02,50 34568901,2025-01-05,130 12345678,2025-01-03,20 23456789,2025-01-03,60 23456789,2025-01-05,70 34568901,2025-01-02,80 34568901,2025-01-01,90 34568901,2025-01-02,100 34568901,2025-01-03,110 23456789,2025-01-06,40 34568901,2025-01-05,130 34568901,2025-01-06,140 ]; lEFT JOIN(TEST) //precedent load to filter the unwanted records load * where record_flag = 1; //precedent load to flag the record we need LOAD if(AGENT_CODE <> previous(AGENT_CODE),1,0) as record_flag, * RESIDENT TEST2 order by AGENT_CODE, DATE; DROP TABLE TEST2;

Hope this helps.
Best regards,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

Re: First Record required from the data based on criteria

P_Kale — Thu, 08 Jan 2026 05:48:22 GMT

Thanks @RafaelBarrios

I am having AGENT_CODE is in text so how to do sorting.

Can you pl help

Re: First Record required from the data based on criteria

RafaelBarrios — Thu, 08 Jan 2026 12:51:00 GMT

this should do it, no matter if is text or number

TEST2: LOAD AGENT_CODE, "DATE", AMOUNT FROM [lib://DataFiles/TEST2.qvd] (qvd); lEFT JOIN(TEST) //precedent load to filter the unwanted records load * where record_flag = 1; LOAD if(AGENT_CODE <> previous(AGENT_CODE),1,0) as record_flag, * RESIDENT TEST2 order by AGENT_CODE, DATE; DROP TABLE TEST2;

1. Load the QVD

2. load again by resident and use the order by statement (you cant sort when loading qvd)

3. drop the loaded table from QVD

Hope this helps,
best,

Re: First Record required from the data based on criteria

P_Kale — Fri, 09 Jan 2026 11:10:14 GMT

Hi @RafaelBarrios Thank you.

Re: First Record required from the data based on criteria

RafaelBarrios — Fri, 09 Jan 2026 12:17:49 GMT

anytime !!!

best regards,