Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Question about recursive use of left function.

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

marcel_olmo

Partner Ambassador

2010-09-19
12:31 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Question about recursive use of left function.

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.

467 Views

1 Solution

Accepted Solutions

Anonymous

Not applicable

2010-09-19
01:25 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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;

321 Views

2 Replies

Anonymous

Not applicable

2010-09-19
01:25 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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;

322 Views

marcel_olmo

Partner Ambassador

2010-09-19
05:02 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!!

321 Views