topic find missing numbers between variables in App Development

find missing numbers between variables

Ribeiro — Thu, 28 Apr 2022 19:39:16 GMT

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

Re: find missing numbers between variables

vinieme12 — Thu, 28 Apr 2022 20:16:08 GMT

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);

Re: find missing numbers between variables

Ribeiro — Thu, 28 Apr 2022 21:05:08 GMT

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

Re: find missing numbers between variables

vinieme12 — Fri, 29 Apr 2022 02:19:49 GMT

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;

Re: find missing numbers between variables

Ribeiro — Fri, 29 Apr 2022 11:05:24 GMT

😀🙌