Use calculated field (fieldlist) within same load

[panula007]

Hi to all,

I want to ask if is possible (if yes - how) in load statement created new fieldlist (variable) and then use this new fieldlist in same load script. It does not matter if in same table or not, however in loadscript (in order to dont write script in objects).

Concrete example below, I want to use newly created fieldlist in loadscript (separatedproducts) in any other functions in load script (if for example)

Example:

Table1:

load

[Delivery Method],

[products],

SubField([products],',') as[separatedproducts]

IF((WILDMATCH([separatedproducts], '*a') or WILDMATCH([separatedproducts], '*b?*') or WILDMATCH([separatedproducts], '*c*'),'Data',) as [newdata]

from....

Thank you guys!

Vít

[sunny_talwar]

May be you want one of these...

Table1:

LOAD [Delivery Method],

     [products],

     SubField([products],',') as [separatedproducts],

     IF((WILDMATCH([products], '*a') or WILDMATCH([products], '*b?*') or WILDMATCH([products], '*c*'),'Data',) as [newdata]

from....

Or this

Table1:

LOAD [Delivery Method],

     [products],

     SubField([products],',') as [separatedproducts],

     IF((WILDMATCH(SubField([products],','), '*a') or WILDMATCH(SubField([products],','), '*b?*') or WILDMATCH(SubField([products],','), '*c*'),'Data',) as [newdata]

from....

or this

Table1:

LOAD *,

     IF((WILDMATCH(separatedproducts, '*a') or WILDMATCH(separatedproducts, '*b?*') or WILDMATCH(separatedproducts, '*c*'),'Data',) as [newdata];

LOAD [Delivery Method],

     [products],

     SubField([products],',') as [separatedproducts]

from....

[panula007]

Hi Sunny,

unfortunately none of these mentioned. I need to create [separatedproducts] and in same load sript use this in other functions (mentioned "if" for example).

[separatedproducts] is not any column in Excel table or database I am creating it directly in load script and want to use it in later phase of same load script, however last option which you described is not feasiable as Qlik will reply that

[separatedproducts] are not defined in load file.

Thanks,

Vít

[sunny_talwar]

Would you be able to share a few rows of sample data and the expected output from the data?

[panula007]

Hi Sunny,

this is table of source:

Column A: ID Number
Column B: ID Number2

Column C: Products (in format: Product A, Product B, Product C... seprated by comma)

What I need is separate rows with multiple values in column C into single rows. So if in column C is in 1 row e.g. 3 products separated by columns I need to create 3 rows with same ID Number. Thats why I am using SubField, this is ok.

What I need is that in load script I will use subfield in order to seprated it and in same time in same load script I want to use newly fieldset ( separatedproducts) use in other functions in same load script. What i want to do with ifs is that if in separatedproducts is product A call it as e.g. chair, if product B, call it as table and if C call it as table2 and chair, table and table 2 call new fieldset as furniture.

Hope it is more clear now

[Digvijay_Singh]

This below is breaking the basic law, you cannot use calculated field name in the same load, you can use separatedproducts in the preceding load or resident load, like stalwar1 used preceding load in 3rd option -

If you want to calculate more fields then somehow you need to play with [products] field only.

Table1:

load

[Delivery Method],

[products],

SubField([products],',') as[separatedproducts]

IF((WILDMATCH([separatedproducts], '*a') or WILDMATCH([separatedproducts], '*b?*') or WILDMATCH([separatedproducts], '*c*'),'Data',) as [newdata]

from....

[panula007]

Hi,

yes I need to use resident load, this is what I did not know before. Just I did not see "resident" function to call it in mentioned examples. Thank you guys.