topic Re: Use calculated field (fieldlist) within same load in QlikView

Use calculated field (fieldlist) within same load

panula007 — Tue, 20 Feb 2018 14:13:54 GMT

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

Re: Use calculated field (fieldlist) within same load

sunny_talwar — Tue, 20 Feb 2018 17:26:41 GMT

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

Re: Use calculated field (fieldlist) within same load

panula007 — Tue, 20 Feb 2018 18:03:46 GMT

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

Re: Use calculated field (fieldlist) within same load

sunny_talwar — Tue, 20 Feb 2018 18:06:05 GMT

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

Re: Use calculated field (fieldlist) within same load

panula007 — Tue, 20 Feb 2018 22:02:22 GMT

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

Re: Use calculated field (fieldlist) within same load

Digvijay_Singh — Wed, 21 Feb 2018 05:18:42 GMT

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

Re: Use calculated field (fieldlist) within same load

panula007 — Wed, 21 Feb 2018 09:20:34 GMT

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.