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

[cdss-developer]

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;

[marcus_sommer]

I think I would add some TRACE statements with the variables to ensure their content. Also uncommenting the most inner statements (and their related ones - means if you don't create a table you could drop them afterwards) and to watch then the results is often helpful to find the issue - probably just a small thing ... and if it runs too long just reduce the tables for the testing.

- Marcus