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]

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]

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 · Accepted solution]

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

- Marcus

[cdss-developer]

That's it, thanks! 🙂

Eelco

[Zaga_69]

Hi,

I have a similar request. I would like to load and saved tables according to a excel table. 

For example, the image below shows the field I would like to have by Plant (i.e. HEP, HEN, etc)

Then I would like to have sth like this:

Plant_Name:
LOAD
(here I would like to have the selection made in the excel)
From []
WHERE Plant_Name=(the correspoding plant);
STORE Plant_Name INTO [] (qvd);

note: my data source is QVD file which has all plants and all fields

Any idea?

thanks!

[marcus_sommer]

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 ... ?

- Marcus

[Zaga_69]

Hi Marcus,

What I want to do is to link an Excel table (control table) which tell me which fields should be placed in the load statement for each company. 

In the image below is shown the Excel file structure. 

Then, I would like to create a code which allows me to create a QVD file by Company and selecting ONLY the fields already selected by Company in the excel file.

For example, for Company 2 I should have one QVD with 4 fields and for Company 3 one QVD with 23 fields.

Thanks

Edi

[marcus_sommer]

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.

- Marcus

[Zaga_69]

Hi Marcus,

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.

Any idea?

Thanks in advance

Edi