topic Re: Variable problem in QlikView

Variable problem

datanibbler — Fri, 12 Jun 2015 08:47:23 GMT

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

Re: Variable problem

swuehl — Fri, 12 Jun 2015 08:55:43 GMT

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?

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 09:06:32 GMT

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.

Re: Variable problem

swuehl — Fri, 12 Jun 2015 09:20:00 GMT

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.

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 09:24:41 GMT

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.

Re: Variable problem

swuehl — Fri, 12 Jun 2015 09:30:41 GMT

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

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 09:53:58 GMT

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)

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 11:38:39 GMT

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

Re: Variable problem

swuehl — Fri, 12 Jun 2015 11:42:07 GMT

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

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 11:50:35 GMT

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.

Re: Variable problem

swuehl — Fri, 12 Jun 2015 12:39:09 GMT

Your fields all have the same number of subfield items and you want in your output table the subfield items aligned, that is only records with first item of field1, first item field2, first item field3 etc.

Maybe like this:

LOAD *

where ID2 = (RowNo() *F1*F2 + RowNo()*F3 + RowNo()+1);

LOAD *, recno() as ID2;

LOAD

Claim_Nr,

Subfield(FKolli, ' | ') as FKolli_temp,

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

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

substringcount([FKolli],' | ')+1 as F1,

substringcount([field2],' | ')+1 as F2,

substringcount([field3],' | ')+1 as F3

INLINE [

Claim_Nr, FKolli, field2, field3

1, "A1 | A2 | A3 | A4", "B1 | B2 | B3 | B4", "C1 | C2 | C3 | C4"

]

;

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 13:01:16 GMT

Thanks a lot!

I will take a while to understand what this code is doing. There is some mathematics behind it, of course. I had a similar solution to the cartesian product when many-joining two tables, but this is different.

Let's see. I'll keep you posted.

P.S.: Okay, it seems to do exactly what it is supposed to.

I guess if i were to add one more field to that - I do have one more actually, I just omitted it in my question - I will have to somehow adapt that logic? I haven't understood it yet, so I cannot tell how.

Would you mind explaining the logic behind your code?

Thanks a lot!

Best regards,

DataNibbler

Re: Variable problem

swuehl — Fri, 12 Jun 2015 13:54:36 GMT

Just have a look at the table created without the top preceding load with its where clause.

ID2 is just a counter of created lines. Then identify which lines you want to keep and try to build a logic around that.

LOAD *

where ID2 = (RowNo() *F1*F2*F3 + RowNo()*F3*F2 + RowNo()*F4 +rowno()+1);

LOAD *, recno() as ID2;

LOAD

Claim_Nr,

Subfield(FKolli, ' | ') as FKolli_temp,

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

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

Subfield([field4], ' | ') as field4_temp,

substringcount([FKolli],' | ')+1 as F1,

substringcount([field2],' | ')+1 as F2,

substringcount([field3],' | ')+1 as F3,

substringcount([field4],' | ')+1 as F4

INLINE [

Claim_Nr, FKolli, field2, field3, field4

1, "A1 | A2 | A3 | A4", "B1 | B2 | B3 | B4", "C1 | C2 | C3 | C4", "D1 | D2 | D3 | D4"

]

;

Re: Variable problem

datanibbler — Fri, 12 Jun 2015 14:21:03 GMT

This is too much for a Friday afternoon in an office with approx. 27° ... I'll think about it over the weekend.

Re: Variable problem

swuehl — Fri, 12 Jun 2015 14:24:47 GMT

That's probably a good idea.

Don't get confused with the different fields F1,F2,F3,F4. Since this approach only really works with fields having same number of subitems, all 4 numbers are equal (and can be replaced by using just one of them).

Re: Variable problem

datanibbler — Tue, 16 Jun 2015 08:11:15 GMT

Hi swuehl,

I have now removed F2, F3 and F4 (renamed them all to F1)´, but I still don't understand. I'm not much of a mathematician. My uncle was, but I clearly did not inherit this - for which I am thankful in other ways 😉

Would you mind helping me understand this?

So, if I assume that I have 10 elements, then

- when rowNo() is 1 (in record #1), that means ID2 = 1*10*10 + 1*10*10 + 1*10 + 1+1 = 212

- when rowNo() is 2 (in record #2), it means ID2 = 2*10*10 + 2*10*10 + 2*10 + 2+1 = 423

- when rowNo() is 3 (in record #3) it means ID2 = 3*10*10 + 3*10*10 + 3*10 + 3 + 1 = 634

OK, so it always adds 211, starting at 212 - but why?

I would have thought that with 10*10*10*10 = 10.000 records, then every 1000th record, everything would be new, so I'd load records 1, 1001, 2002, 3003 etc.

There seems to be an error in the logic, too - the table (with 14 items) still has 95 lines - but some items seem to be there several times, so I have to clear that up before I can tell you what exactly the problem is.

Thanks a lot!

Best regards,

DataNibbler

P.S.: Okay, there are 13 different sub_items in that item. But 13*13 is not 95, so I'm not quite sure what is wrong.

Re: Variable problem

Tue, 16 Jun 2015 08:26:35 GMT

nice thanks

Re: Variable problem

datanibbler — Tue, 16 Jun 2015 14:01:31 GMT

Hi,

okay, I have come up with some alternative logic. I build my own list of records that I want to extract.

When there are four elements, for instance, this list looks like

>> 1, 65, 129, 193 <<

How can I now build a LOAD that extracts (RESIDENT) only these records?

I have tried with a MATCH() which should work when I have a list of values, but something is clearly wrong.

Can anyone help me here? There must be some stupid mistake in my syntax, it's really annoying.

Thanks a lot!

Best regards,

DataNibbler

P.S.: OK, I have found my error. Now the whole thing works.

The logic is this:

- If there are 4 elements, I want records 1, 65, 129 and 193 (out of 256)

- If there are 3, I want records 1, 28 and 55 (out of 81)

- If there are 2, I want records 1 and 9

Re: Variable problem

swuehl — Tue, 16 Jun 2015 14:25:04 GMT

Yes, your filter needs to adapt to your number of fields and field elements. That's where somewhat complicated formula comes from.

And regarding your post earlier this day, a little hint: rowno() will not return 1, but zero when used in a where clause for the first record coming in, until there is actually a record in the output table.

Re: Variable problem

datanibbler — Wed, 17 Jun 2015 06:30:38 GMT

Thanks!

I noticed there was something wrong. Now I have two LOADs - the first returns that cartesian product, but also a Line_ID, and the second RESIDENT (I try to avoid the preceding because it's harder for others to understand) then extract just the records I want - I have an inbetween step where I calculate, in a loop, which records I want to extract.

Now it works fine.

Thanks a lot!

Best regards,

DataNibbler