Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'll show you what I'm trying to do :
This is an image of my desired result :
Here you can find the two key fields : N_Fila and N_Fila_Cuenta.
N_Fila_Cuenta is the important one, because I want to divide all the numbers of this field in "levels" like I've shown you before in the picture.
By now, I'm doing it in a static way, wich means I use the left() function to get the desired results because I know exactly that the last level will be level 7.
This is how I'm doin it now :
Cuentas_Mayor8:
load
N as N_Fila,
N2 as N_Fila_Cuenta,
left(N2,1) as "IDCuentaNivel1",
left(N2,2) as "IDCuentaNivel2",
left(N2,3) as "IDCuentaNivel3",
left(N2,4) as "IDCuentaNivel4",
left(N2,5) as "IDCuentaNivel5",
left(N2,6) as "IDCuentaNivel6",
left(N2,7) as "IDCuentaNivel7"
resident Cuentas_Mayor7;
But in the future I won't know how many numbers will have the field "N_Fila_Cuenta", which means I won't know how many levels I'll have.
Anybody has an idea how to that in a dynamic way? I appreciate any kind of help.
Thanks in advance.
Regards.
If the lenftgh of all values is the same, the next script can be used (or similar to this):
Cuentas_Mayor7: // this is just for illustration
load * inline [
N, N2
10, 6030001
10, 6030002];
// Example starts here
LET L = len(peek('N2')); // define length
//
NOCONCATENATE
Cuentas_Mayor8:
LOAD
N,
N2
resident Cuentas_Mayor7;
//
for l = 1 to $(L)
JOIN (Cuentas_Mayor8) LOAD
N,
N2,
left(N2,$(l)) as IDCuentaNivel$(l)
RESIDENT Cuentas_Mayor7;
next
//
DROP TABLE Cuentas_Mayor7;
If the lenftgh of all values is the same, the next script can be used (or similar to this):
Cuentas_Mayor7: // this is just for illustration
load * inline [
N, N2
10, 6030001
10, 6030002];
// Example starts here
LET L = len(peek('N2')); // define length
//
NOCONCATENATE
Cuentas_Mayor8:
LOAD
N,
N2
resident Cuentas_Mayor7;
//
for l = 1 to $(L)
JOIN (Cuentas_Mayor8) LOAD
N,
N2,
left(N2,$(l)) as IDCuentaNivel$(l)
RESIDENT Cuentas_Mayor7;
next
//
DROP TABLE Cuentas_Mayor7;
Thanks Michael. Your post was very helpful.
I didn't know that you can loop a recently loaded table like this.
That was exactly what I was looking for.
Thank you and see you around!!