First Record required from the data based on criteria

[P_Kale]

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;

[RafaelBarrios · Accepted solution]

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!

[P_Kale]

Thanks @RafaelBarrios 

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

Can you pl help

[RafaelBarrios]

Hi

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,

[P_Kale]

Hi @RafaelBarrios Thank you.

[RafaelBarrios]

anytime !!!

best regards,