topic Re: How to fetch a whole front-end Field in loading script? in QlikView

How to fetch a whole front-end Field in loading script?

thomas_wang — Thu, 06 Sep 2018 08:48:58 GMT

My title may confuse you, I think I should explain it.

This idea comes from debugging EXISTS() function. The enumeration of the first parameter is what I need to fetch, but I found it difficult. Let's start with a simple example.

A_B:

LOAD * Inline [

A, B

1, x

2, x

];

A_C:

LOAD * Inline [

A, C

2, x

3, x

];

Here I want fetch the enumeration of A. A common practice is like this:

LOAD A as Enu Resident A_B;

Concatenate LOAD A as Enu Resident A_C;

But this method requires you to know which tables the field A comes from. Sometimes this is very difficult, especially when the labels of these tables are not explicitly defined.

Another way is to use "EXIT Script" to terminate the script here, and then use the list box to view it at the front end. But this only allows you to see them and they can't be used in scripts.

Re: How to fetch a whole front-end Field in loading script?

petter — Thu, 06 Sep 2018 09:20:15 GMT

If you don't know the tables it is sufficent to know the field name by using the FieldValue() function along with the FieldValueCount(). This will also give you distinct values which the concatenate approach doesn't.

It can work well for smaller enumerations but not necessarily on very large number of enumeration values...

FOR index=1 TO FieldValueCount('A')

value = FieldValue('A',index);

ENU: LOAD $(value) AS Enu AUTOGENERATE 1;

If the field is a text you will have to enclose the $(FieldValue) in single quotation marks: LOAD '$(value)'

EDITED: look for the next answer below....

It might be quicker to do this if it works (haven't tested it but I think it would work):

FieldValueCount = FieldValueCount('A');

ENU:

LOAD

Evaluate('FieldValue(''A'',IterNo()) AS Enu

AUTOGENERATE

$(FieldValueCount);

Re: How to fetch a whole front-end Field in loading script?

petter — Thu, 06 Sep 2018 09:35:07 GMT

Forget the last part I suggested above - this will work:

FieldValueCount = FieldValueCount('A');


ENU:
LOAD
  Evaluate( FieldValue('A',RecNo()) ) AS Enu
AUTOGENERATE
  $(FieldValueCount);

And it should be 100 times as fast as a FOR ... NEXT loop approach I mentioned first...

Re: How to fetch a whole front-end Field in loading script?

thomas_wang — Fri, 07 Sep 2018 01:13:58 GMT

Yes it works, thank you very much!

I've simplified your script.

LOAD Evaluate(FieldValue('A',RecNo())) AS Enu
AutoGenerate FieldValueCount('A');

I have looked at the FieldValue () function in the help document. The document writes, "Note: This function will only work with distinct field values." I misunderstood this sentence and thought that if the field contained duplicate data, the function would not work

Re: How to fetch a whole front-end Field in loading script?

thomas_wang — Fri, 07 Sep 2018 02:23:45 GMT

Hi Petter,

I noticed that you used the Evaluate() function. If there are non numeric type data in field A, these data will be lost. I tried to get rid of this function, but all numeric type data shows question marks in front end. I wonder why.

Thanks!

Re: How to fetch a whole front-end Field in loading script?

petter — Fri, 07 Sep 2018 05:10:32 GMT

I don't know why I used the Evaluate() in the first place ... it is actually not needed.

This will work with any type of field content:

LOAD
  If( IsNum(FieldValue('A',RecNo())), FieldValue('A',RecNo()) , Text(FieldValue('A',RecNo())) ) AS Enu
AUTOGENERATE FieldValueCount('A');

Re: How to fetch a whole front-end Field in loading script?

thomas_wang — Fri, 07 Sep 2018 05:53:59 GMT

I thought simply remove Evaluate() is fine. The data is correct in the background.

LOAD FieldValue('A',RecNo()) AS Enu
AutoGenerate FieldValueCount('A');

About the front-end display problem, though I don't know the exact reason, but I think your code should like below.

LOAD If(IsNum(FieldValue('A',RecNo()))
,Num(FieldValue('A',RecNo()))
,FieldValue('A',RecNo())) AS Enu
AutoGenerate FieldValueCount('A');

Re: How to fetch a whole front-end Field in loading script?

petter — Fri, 07 Sep 2018 06:08:33 GMT

I am happy that you have a use for the suggestions I made... it is not "my code" as you suggest and you can do whatever you want with it and see fit for your use cases....

Re: How to fetch a whole front-end Field in loading script?

thomas_wang — Fri, 07 Sep 2018 06:34:03 GMT

I think there is no problem now. Thank you very much for your patience and help.