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

Discussion Board for collaboration related to QlikView App Development.

Announcements

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: **REGISTER NOW!**

- 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 - Specialist II

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.

269 Views

1 Solution

Accepted Solutions

mov

Employee

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;

123 Views

2 Replies

mov

Employee

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;

124 Views

marcel_olmo

Partner - Specialist II

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

123 Views