find missing numbers between variables

[Ribeiro]

I need to create ranges in the variable to find values that are missing in the branch and in the sequence.

vMIN: 1500

vMAX: 1510

tmp:
Load * inline [
branch, number, 

1,1501
1,1503
1,1504
1,1505
2,1504
2,1506
2,1507
];

resultado:

número da agência, 

1,1502

2,1505

[vinieme12 · Accepted solution]

let vMIN= 1500;

let vMAX= 1510;

tmp:
Load * inline [
branch, number
1,1501
1,1503
1,1504
1,1505
2,1504
2,1506
2,1507
];

Temp2:
Load
rowno() as Srno
,Number2
Where not exists (number,Number2)
;
Load
$(vMIN) +ITERNO()-1 as Number2
AUTOGENERATE 1
While $(vMIN) +ITERNO()-1 -$(vMAX);

[Ribeiro]

Vineeth Pujari,

It turned out really good. But now I see that it lacked to separate by branch. same in the result below. and also use a table of starting and ending number per branch.

tmp:
Load * inline [
branch, number, 
1,1501
1,1503
1,1504
1,1505
2,2504
2,2506
2,2507
];

tmp2:
Load * inline [
branch, vMin, vMax
1,1500,2600
2,2500,2600
];

result:

branch, number, 

1,1502

2,2505

[vinieme12 · Accepted solution]

as below

tmp:
Load *,branch&'-'&number as key inline [
branch, number
1,1501
1,1503
1,1504
1,1505
2,1504
2,1506
2,1507
];

Temp2:
Load
branch2
,Number2
Where Not Exists(key,branch2&'-'&Number2)
;
Load
branch2
,min+Iterno()-1 as Number2
While min+Iterno()-1 <= max
;
Load
branch as branch2
,max(number) as max
,min(number) as min
Resident tmp
Group by branch;

exit Script;

[Ribeiro]

😀🙌