Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

cdss-developer
Not applicable

Dynamically load and fill variables from a table (through a loop)

Hello,

I made this dashboard that helps me find certain combinations (text and numbers) from large pieces of text (Table: 'DATA''). This is just an example. In this case I'm always looking for a combination of two characters with variable amount of numbers.

This works fine. However, I have a lot of these combinations (see TABLE1, this is just a small selection) and I do not want to copy the same code every time. Like I do right now, by each time: filling it with other characters and another amount of  digits. It would be nice, that I just have to alter TABLE1 or can  reload a Excel file.

It would be nice that the 'RETRIEVAL' code goes through a loop and that the two characters and the amount of characters total presented are loaded dynamically from the TABLE1-table (TABLE1).

I hope my explanation is clear, otherwise please let me know.
Can anyone please help me achieve this?

Kind regards,

Eelco

 

 

 

//Code:

DATA:
LOAD * INLINE [
ID, TEXT
1, 904OIE4Q09AKJN3QLKNMFWH9846DJKJWL45
2, djalkkj489uquirfhe43hjkDE412657djwe
3, lokjerjAQkoiKLDSp34ojWE5787884565456218iuafjklohasfdkjWH9845djkw456
4, lkjslkjkjlwe89iQF845698752sdfjkle
5, jlkkjl0923jkkllkdjfjkljkl545654ads
];


TABLE1:
LOAD * INLINE [
CODE, LENGTH
DE, 8
WH, 6
QF, 9
WE, 10
];

RETRIEVAL:
LOAD
ID, mid([TEXT],Index([TEXT],'DE')+0,8) AS RESULT
Resident DATA;

LOAD
ID, mid([TEXT],Index([TEXT],'WH')+0,6) AS RESULT
Resident DATA;


LOAD
ID, mid([TEXT],Index([TEXT],'QF')+0,9) AS RESULT
Resident DATA;


LOAD
ID, mid([TEXT],Index([TEXT],'WE')+0,10) AS RESULT
Resident DATA;

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer
Not applicable

Re: Dynamically load and fill variables from a table (through a loop)

It was just a small typo because the table is TABLE1 and not TABLE:

TABLE1:
LOAD * INLINE [
CODE, LENGTH
DE, 8
WH, 6
QF, 9
WE, 10
];

for i = 0 to noofrows('TABLE1') - 1            
   vChar = peek('CODE', $(i), 'TABLE1');
   vLength = peek('LENGTH', $(i), 'TABLE1');

   RETRIEVAL: LOAD ID, mid([TEXT],Index([TEXT],'$(vChar)')+0,$(vLength)) AS RESULT,
   rowno() as RowNo, $(i) as LoopIteration, '$(vChar)' as vChar, $(vLength) as vLength
   Resident DATA;
next

CharLoop.JPG

- Marcus

4 Replies
Highlighted
marcus_sommer
Not applicable

Re: Dynamically load and fill variables from a table (through a loop)

You could use such table and looping through all values, like:

for i = 0 to noofrows('TABLE1') - 1
   vChar = peek('CODE', $(i), 'TABLE');
   vLength = peek('LENGTH', $(i), 'TABLE');

   RETRIEVAL: LOAD ID, mid([TEXT],Index([TEXT],'$(vChar)')+0,$(vLength)) AS RESULT
   Resident DATA;
next

By larger datasets in one or maybe both tables such looping might need some times. An alternatively might be to change the string with mapsubstring() and to pick the needed parts afterwards but depending of your real requirements it might not very easy to apply such a pick logic.

- Marcus

cdss-developer
Not applicable

Re: Dynamically load and fill variables from a table (through a loop)

Hi Marcus,

Thank you for your response!

I have changed the code, but unfortunately it only seems to work partially (see attached).
It only returns 'DE412657'.  And I would expect that it also returns 'QF8456987', 'WE57878845' and 'WH9845' as RESULT.

I think doing something wrong, can you help me further?

Thanks.
Eelco

marcus_sommer
Not applicable

Re: Dynamically load and fill variables from a table (through a loop)

It was just a small typo because the table is TABLE1 and not TABLE:

TABLE1:
LOAD * INLINE [
CODE, LENGTH
DE, 8
WH, 6
QF, 9
WE, 10
];

for i = 0 to noofrows('TABLE1') - 1            
   vChar = peek('CODE', $(i), 'TABLE1');
   vLength = peek('LENGTH', $(i), 'TABLE1');

   RETRIEVAL: LOAD ID, mid([TEXT],Index([TEXT],'$(vChar)')+0,$(vLength)) AS RESULT,
   rowno() as RowNo, $(i) as LoopIteration, '$(vChar)' as vChar, $(vLength) as vLength
   Resident DATA;
next

CharLoop.JPG

- Marcus

cdss-developer
Not applicable

Re: Dynamically load and fill variables from a table (through a loop)

That's it, thanks! :-)

Eelco