Dynamically load and fill variables from a table (through a loop)
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?
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.
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?
On a first look it seems not very similar to the origin query else creating of multiple load-statements with conditions on the fly. However it's quite unclear how the conditions should be applied - here specified as a crosstable and anyhow on a field-level ... ?
It's just written down but I think the main-logic might be look like the following:
t1: crosstable(Company, Value, 2) load * from Excel;
for i = 1 to fieldvaluecount('Company') let vCompany = fieldvalue('Company', $(i)); t2: load concat(Fields, ', ') as FieldsSelected from t1 where Company = '$(vCompany)' and Value = 'x'; let vFields = fieldvalue(FieldsSelected, 1); t3: load '$(vFields) from QVD where Company = '$(vCompany)' ; store t3 into $(vCompany).qvd (qvd); drop tables t3, t2; next
You may need some adjustments here and there especially if you want to include the tags and/or that there are spaces or special chars within the fieldnames which requires an appropriate wrapping with [ it might be sensible to add it within the excel ] and/or if there should be some renaming, too or something similar.
Further it might be useful not to try the whole approach at once else just to TRACE the loop and the variables within the debugger to check that the results really look as if you had written them manually.
Thank you so much! it has been very helpful, however still I have mistake.
As you can observe, apparently the variable vFields is not working properly. Apparently, there is an extra comma ... To make sure the variable vFields takes the right fields, I checked values in Table_2 and the Fields selected are separated by comma and the last field it is w/o comma, which is correct.