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

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

cdss-developer — Sat, 16 Nov 2024 04:48:42 GMT

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;

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

marcus_sommer — Thu, 10 Jan 2019 12:58:57 GMT

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

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

cdss-developer — Mon, 14 Jan 2019 08:27:26 GMT

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

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

marcus_sommer — Mon, 14 Jan 2019 09:51:46 GMT

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

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

cdss-developer — Mon, 14 Jan 2019 09:54:24 GMT

That's it, thanks! 🙂

Eelco

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

Zaga_69 — Sat, 24 Aug 2019 01:51:41 GMT

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!

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

marcus_sommer — Mon, 26 Aug 2019 06:59:07 GMT

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

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

Zaga_69 — Mon, 26 Aug 2019 09:18:57 GMT

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

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

marcus_sommer — Mon, 26 Aug 2019 10:57:20 GMT

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

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

Zaga_69 — Mon, 26 Aug 2019 14:24:05 GMT

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

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

marcus_sommer — Mon, 26 Aug 2019 14:39:14 GMT

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