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

[thomas_wang]

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.

[petter]

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;

NEXT

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);

[petter]

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

[thomas_wang]

Yes it works, thank you very much!

I've simplified your script.

1. LOAD Evaluate(FieldValue('A',RecNo())) AS Enu
2. 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

[thomas_wang]

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!

[petter]

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');

[thomas_wang · Accepted solution]

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

1. LOAD FieldValue('A',RecNo()) AS Enu
2. 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.

1. LOAD If(IsNum(FieldValue('A',RecNo()))
2.        ,Num(FieldValue('A',RecNo()))
3.        ,FieldValue('A',RecNo())) AS Enu

4. AutoGenerate FieldValueCount('A');

[petter]

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

[thomas_wang]

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