Based on min value of seq number for Key , create new table less than target sequence number,

[gopal5879]

Hello folks Need little help, I am scratching my head about doing this. I have table Triage_Claim , I can find target rows for each of triage_Claim_id key in table Triage_Claim 

I am trying to create table Triage_Review from Triage_Claim with seqno less than target for each of triage_claim_id  values.

Can someone offer me clue how to achieve this?  Thanks a lot.

Triage_Claim
Triage_claim_id	seqno
1001	1
1001	2
1001	3	target
1001	4
1001	5
1002	1
1002	2	target
1002	3
Triage_Review
Triage_claim_id	seqno
1001	1
1001	2
1002	1

[jwjackso]

Using the data below:

Triage_Claim:
LOAD * Inline [
Triage_claim_id,seqno,target_fld
1001,1,''
1001,2,''
1001,3,'target'
1001,4,''
1001,5,''
1002,1,''
1002,2,'target'
1002,3,''
];

mapTriage_Review:
Mapping
LOAD Triage_claim_id,
seqno
Resident Triage_Claim where target_fld = 'target';


Triage_Review:
Load Triage_claim_id,
seqno,
TriageFLG
where TriageFLG = 'Y';
LOAD Triage_claim_id,
seqno,
If(ApplyMap('mapTriage_Review',Triage_claim_id) > seqno,'Y','N') as TriageFLG
Resident Triage_Claim;

You can filter on the triageFLG:

[Taoufiq_Zarra]

One Option :

Triage_Claim:

LOAD * INLINE [
    Triage_claim_id , seqno, target 
    1001, 1,  
    1001, 2,  
    1001, 3, target 
    1001, 4,  
    1001, 5,  
    1002, 1,  
    1002, 2, target 
    1002, 3,  
];

Triage_Review_tmp:

load Triage_claim_id , seqno resident Triage_Claim;
left join
load Triage_claim_id , seqno as S  resident Triage_Claim where target='target';

Triage_Review :

load Triage_claim_id , seqno resident  Triage_Review_tmp where seqno<=S;

drop table Triage_Claim,Triage_Review_tmp;

output :

[Saravanan_Desingh]

One solution in front-end is:

tab1:
LOAD * INLINE [
    Triage_claim_id , seqno, target 
    1001, 1,  
    1001, 2,  
    1001, 3, target 
    1001, 4,  
    1001, 5,  
    1002, 1,  
    1002, 2, target 
    1002, 3,  
    1003, 1,  
    1003, 4, target 
    1003, 3,      
];

Dimension: Triage_claim_id , seqno

Expression: If(seqno<Only({<target={'target'}>}TOTAL <Triage_claim_id> seqno),seqno)

[Saravanan_Desingh]

Output.

[Brett_Bleess]

You have received multiple responses to your post, we would greatly appreciate it if you would return to the thread and close things out by using the Accept as Solution button on the post(s) that helped you get things sorted.  This gives credit to the poster(s) and lets the other Members know what worked.  If you did something different, you can post what you did and then mark that post, and if you have further questions, please leave an update.

Regards,
Brett