Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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;
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);
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
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;
😀🙌