Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So i have a Table box that contains a single field "Numb":
Sample Data:
Numb
A1001 |
A1002 |
A1003 |
A1004 |
A1005 |
A1006 |
A1007 |
A1008 |
A1009 |
A1010 |
A1011 |
A1012 |
A1014 |
A1015 |
A1016 |
A1017 |
A1018 |
A1020 |
I want to have a second table box (or straight table whatever works) with an expression that shows the row before and the row after any missing rows that don't run on in green and then the missing row in red.
Expected Output:
CheckNumb
A1012
A1013
A1014
A1018
A1019
A1020
Solved this by adding this to the load script:
purgechar(Numb,'A') AS NumbOnly
Then adding a straight table with a dimension of:
NumbOnly
And the expression using the `ABOVE` function:
=IF(NumbOnly-Above(NumbOnly) = 1 ,'Match','Check')
Hi Matthewp,
I just want to confirm, the number suppose is missing in your column that is Numb then is it defined as
'Null' or '-' or empty cell into your data source (whatever it would be either excel or sql table ) ?
Like is your column values like as below :
A1001 |
A1002 |
A1003 |
A1004 |
A1005 |
A1006 |
A1007 |
A1008 |
A1009 |
A1010 |
A1011 |
A1012 NULL |
A1014 |
A1015 |
A1016 |
A1017 |
A1018 NULL |
A1020 |
OR
A1001 |
A1002 |
A1003 |
A1004 |
A1005 |
A1006 |
A1007 |
A1008 |
A1009 |
A1010 |
A1011 |
A1012 - |
A1014 |
A1015 |
A1016 |
A1017 |
A1018 - |
A1020 |
Regards,
Mahamed
neither. its just simply not there
Hi
Try like this
Temp:
LOAD *, KeepChar(Test, 0123456789) As Test1 Inline
[
Test
A1001
A1002
A1003
A1004
A1005
A1006
A1007
A1008
A1009
A1010
A1011
A1012
A1014
A1015
A1016
A1017
A1018
A1020
];
MinMaxTemp:
Load Min(Test1)-1 As Min, Max(Test1) as Max Resident Temp;
Let vMin = Peek('Min');
Let vMax = Peek('Max');
Join(Temp)
LOAD $(vMin)+IterNo() as Test1 AutoGenerate 1 While $(vMin)+IterNo() <= $(vMax);
Final:
LOAD Test, Test1, if(Isnull(Test), 1 ) AS Flag Resident Temp;
DROP Table Temp;
Output:
Test | Test1 | Flag |
---|---|---|
A1001 | 1001 | |
A1002 | 1002 | |
A1003 | 1003 | |
A1004 | 1004 | |
A1005 | 1005 | |
A1006 | 1006 | |
A1007 | 1007 | |
A1008 | 1008 | |
A1009 | 1009 | |
A1010 | 1010 | |
A1011 | 1011 | |
A1012 | 1012 | |
1013 | 1 | |
A1014 | 1014 | |
A1015 | 1015 | |
A1016 | 1016 | |
A1017 | 1017 | |
A1018 | 1018 | |
1019 | 1 | |
A1020 | 1020 |
Hi Mattew,
If it is the case then, we cant not add the data into our data model by our own.
But, if you still want to show the data which is not available into your source table then you can use
the master calendar logic which is given by Mayil above.
Regards,
Mahamed
try this
Data:
LOAD purgechar(Numb,'A') as NUMB,
Numb
FROM table;
New:
LOAD Numb,
if(NUMB<>previous(NUMB)+1,previous(NUMB),0) as NewNumbMin,
if(NUMB<>previous(NUMB)+1,NUMB,0) as NewNumbMax
resident Data
order by NUMB ;
Final:
LOAD 'A' & CheckNumb as CheckNumb;
LOAD *,
NewNumbMin +iterno()-1 as CheckNumb
WHILE NewNumbMin +iterno()-1 <= NewNumbMax;
LOAD *
Resident New
WHERE NewNumbMin>0 and NewNumbMax>0;
drop table New;
Take Table box of Numb and Checknumb
Solved this by adding this to the load script:
purgechar(Numb,'A') AS NumbOnly
Then adding a straight table with a dimension of:
NumbOnly
And the expression using the `ABOVE` function:
=IF(NumbOnly-Above(NumbOnly) = 1 ,'Match','Check')