LOAD with a lot of variables

[datanibbler]

Hi,

I have a rather complicated LOAD from an Excel list where each month one column is inserted in a specific place.

To find out whether or not to load certain columns, I have a script working in several steps (I will attach it all in a txt file so that you can reconstruct it)

- First, I have a FIRST1 LOAD so that I know all the fieldnames;

- Then I parse columns 33 (in this instance) to 45 (12 months) and fill two variables for each:

   - If the fieldname is such that it is a real field, the first variable holds its fieldname

   - In that case, the second one holds an alias (the fieldname a bit shorter)

   - If the fieldname does not meet my criteria, both variables are e.g. > dummy_37 <

Once all those variables are filles, I have the actual LOAD with 12 lines (33 to 45) all looking the same (built up in Excel). They work fine in principle but for a detail:

- When I put the field_variable in square brackets (they do have BLANKs), then the script breaks at line 44 (there is no data for Nov and

   thus there is no column) and I get a message "Field not found > dummy_44 <"

- When I put the field_variable in quotes instead, I get only the name of the field in every line, not the value.

Can anyone help me solve that issue?

Thanks a lot!

Best regards,

DataNibbler

[Colin-Albert]

If your data has a separate column for each month would it be simpler to load this as a crosstable load then manipulate the results?

[datanibbler]

Hi Colin,

well, I don't know - but the nr. of columns is not the same. Every month, one column is added to the list.

I think I have to insert one more step before the actual LOAD to decide which field_variable to wrap in square brackets (because of the BLANKs) and which one to wrap in quotes.

[Colin-Albert]

Crosstable will cope with the extra columns, provided there is no other data to the right of the monthly columns.

The new columns will simply add more rows to the output table and will not change the number of columns, so your load script should be much simpler.

You should not need any variables to load the data using crosstable.

[Colin-Albert]

Have a look at this blog

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

[datanibbler]

Hi Colin,

well, but my table is NOT a crosstable in nature. There are no dimension_fields in the first column. There are only the fieldnames in line #1.

[Colin-Albert]

Is your table a single row of data? If so can you add a dummy dimension to get the crosstable working?

Otherwise can you use transpose to swap the rows and columns?  Transformation, Rotate, Transpose.

[datanibbler · Accepted solution]

Hi Colin,

my table has many rows, but there is only one dimension, that is the fieldnames in line1.

However, I have finally figured out how to do it. During the first LOOP where I fill the variables, I used square brackets and the chr() function (for the quotes around the text_in_quotes) to finalize the field_variables so that during the actual LOAD I don't need any IF_clauses, only the $() expansion of the field_variables.

It works fine now.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

One more question - actually something else, but it's related, so rather than explain my entire scenario again, I'll just append it here:

Can you tell me why the following code, designed to look for the first field that does not meet my criteria for a real field, returns me a value of 24 (that is 'dummy_45') when in fact I have a field 'dummy_44' right before that? (so actually the value should be 23

FOR i = 1 TO NoOfFields('Staplergrundkosten')
IF LEFT(FieldName($(i), 'Staplergrundkosten'),5)='dummy' THEN
LET erstes_unbesetztes_Feld = i;
End if
Next i

Thanks a lot!

Best regards,

DataNibbler

[swuehl]

Try

FOR i = 1 TO NoOfFields('Staplergrundkosten')
IF LEFT(FieldName($(i), 'Staplergrundkosten'),5)='dummy' THEN
LET erstes_unbesetztes_Feld = i;

EXIT FOR
End if
Next i