Variable problem

[datanibbler]

Hi,

I have a problem with a variable:

- I now actually prefer defining all the necessary variables in the script - that way I can be sure that I can delete all the variables in the
   variable_manager and be sure that all the necessary ones will be re-created and only the non_necessary ones are gone.

- For variables that are populated on the GUI, there is the syntax (for example)
   >> SET v_myvar = = Getfieldselections(date); <<

- In my current app there are several blocks in the script, each one with a condition for the execution, a variable > v_database_action <

- In one specific block (where $(v_database_action)=6 ) I have to do a RESIDENT LOAD from one other table (that is also loaded in that
   block) with a WHERE condition specifying that one specific field in the table be equal to that variable.

- That variable, though initiated in the script, has in the meantime (through some other action by the user) been filled with a value (and
   that SET command is set to NOT be executed when $(v_database_action)=6, so that it isn't reset.

<=> Still, that WHERE condition fails and when I load without it and I load the value of that variable is exactly that formula, not its output.

The same happens when I define that variable on the GUI. The script, when reloading with $(v_database_action)=6, uses the formula, not the output. Thus of course it cannot find any matching records.

Is there any way around this other than asking the user on the GUI to actually type the nr of the record they want to edit?

Thanks a lot!

Best regards,

DataNibbler

[swuehl]

I somewhere got lost after line 5.

Programmers are more used to read code than prose, would it be possible that you upload a small sample QVW or at least the relevant snippet of your script code?

[datanibbler]

Hi swuehl,

well, not really - I could, but putting together another sample_file would take me about as much time as trying out a few things.

The whole novel boils down to the question whether QlikView can, when reloading the script from the GUI, access and use the current value of a variable that is created with Getfieldselections().

As yet, it seems that doesn't work because QlikView doesn't really register that value. When I somehow quote that variable in the script, QlikView uses the formula (that it was defined with), not the current value.

That is the status of things - now I have to ask the user to type the nr. of the record they want to work on so I have a fixed value that QlikView can use. Not the most elegant solution, and believe it or not, it is still error-prone - typing an 8-digit number might seem easy, but you know maybe how creative people can be when it comes to inventing new ways of doing things wrong 😉

P.S.: Sorry, don't get me wrong, I am well aware that it is me asking help and you trying to give it, so it should be worth my time putting together a sample for you to do so. Let me see. I have made something up yesterday or so, I might be able to re-use that. If I find it works, I will upload it before taking my lunch break.

[swuehl]

You can probably create an additional action before your user triggered reload to copy the content of that variable to another variable, which you can then use in your script.

edit: Yes, seems to work for me.

[datanibbler]

Hi swuehl,

that sounds good - so I can create another variable and just go like

>> SET v_myvar2 = v_myvar <<

?

Do I need to use SET or LET in that case? SET, no?

I'm just working on that sample file and app I had put together yesterday.

There you go. I hope that will serve to illustrate where my problem is, though I think you've already got it.

[swuehl]

I believe the assignment needs to be done in the action.

You need to create the variable before you use it, either in the variable overview, or in the script.

Since you prefer latter, create the variable after you need to use:

Set v_myvar2 = DummyValue;

That means, you need to test the variable if it's the first time you reload or not (or, if you are sure about the consequences, use a default value instead of DummyValue.

Then, in the UI, I assume you have a button with Reload action.

Add another action first place: External -  Set Variable

v_myvar2

=v_myvar

[datanibbler]

Hi,

yes, that seems to work 😉

Great. Now I have a different problem, though, as I get way too many records - that header_line (Claim) has a dozen or so position_lines, but I get about 29k records from that LOAD.

The load has four Subfield() commands (supposed to make a list of values into a number of records).

Could that be a reason? Does that generate some kind of cartesian product? The help says it will generate that if the Subfield() functions are nested, but they are not - they are in different lines of the LOAD and for different fields.

Actually, the nr. of records should of course be the same for all four fields.

The background is, I have aggregated it beforehand, but I want to show a proper listbox with those values.

Or could it be because there is an * in one of the fields?

Thanks a lot!

P.S.: Well, I have removed the * (replaced it with real numbers) and the issue remains, so it must be some cartesian product between the SUBFIELD() functions in the different lines of the LOAD.

How else could I do that?

P.P.S.: Yes, it is a cartesian - the Claim has 13 position_lines and 13 x 4 = 28.561 (or so)

[datanibbler]

Hi swuehl,

can you help me once more?

Just deleting the function so I have just one Subfield() in the LOAD and the three other fields just like that doesn't help - then I get the complete list of values in every record - but I want every record to have its respective value out of that list - so that cartesian product was closer to "correct" ...

[swuehl]

Could you post an example of one of these input records and what you expect to get after the transformation?

[datanibbler]

Hi,

it's actually pretty easy.

I have three fields with lists of values that I have put together in an earlier script_block - each one looks like

>> 1 | 2 | 3 | 4 ... << - there are 13 records (13 position_lines within that one Claim).

When I use Subfield([field], ' | ') for just one of those three fields, thus my LOAD looks like

LOAD

       Claim_Nr,

      Subfield(FKolli, ' | ') as FKolli_temp

RESIDENT ClaimDaten

;

=> Then I do get 13 records, that is correct (I have omitted the WHERE clause here, but I do have that).

If I go like

LOAD

     Claim_Nr,

    Subfield(FKolli, ' | ') as FKolli_temp,

    [field2],

   [field3]

RESIDENT ClaimDaten

;

=> then I do get 13 records, but every one has the complete list of values in [field2] and [field3].

If I go like

LOAD

    Claim_Nr,

    Subfield(FKolli, ' | ') as FKolli_temp,

    Subfield([field2], ' | ') as field2_temp,

    Subfield([field3], ' | ') as field3_temp

RESIDENT ClaimDaten

;

=> Then I get 13 x 13 x 13 records

What I want is 13 records, but each one with its respective value in each of these 3 fields.

Do you have an idea how I can achieve that?

P.S.: When I start with a LOAD with just one Subfield() and then join two others like it (each time using the fct on a different field), the result is the same, I get a cartesian product.

I could use DISTINCT, but only on one out of those three fields for in the others, several out of those 13 records might have the same value.