Create field in script editor according to set analysis formula

[JoannaM]

Hi all

I have created the following formula in set analysis:

if([operation number]=[max operation number] and [confirmed qty position]>0 or [goods issued]>0
,[last OP],CONCAT({<[next OP'S]=>} DISTINCT [next OP'S],'/',[operation number]))

This looks like:

What I am trying to do is to concatenate all the work center operations.

I need to transfer this formula into script editor. How can i transfer the CONCAT({<[next OP'S]=>} DISTINCT [next OP'S],'/',[operation number]) part? I don't know where to start.

Any suggestions? Unfortunately, I can't share any more data.

Thanks for the input.

BR, Joanna

[rwunderlich]

You can use the Concat function in script. You must also include a GROUP BY clause in the load statement to identify the grouping field. Something like:

Load
  Concat(DISTINCT [next OP's], '/') as ConcatenedOperations
Resident mydata
Group By [somedim]
;

I don't know what the "somedim" field is in your data. It's whatever dimension(s) you used in the chart. 

I'll assume [max operation number] was a previously loaded field so you can use exists() for your condition like:

Load
   if(exists([operation number], [max operation number])and [confirmed qty position]>0 or [goods issued]>0
  , [last OP]
  ,Concat(DISTINCT [next OP's], '/', [operation number]) as ConcatenedOperations
Resident mydata
Group By [somedim]
;

This assumes all fields are in the same "mydata" table. If not, see 
https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[JoannaM · Accepted solution]

Hi Rwunderlich

Thanks for your input. With your help I managed to get what I needed.

My script:

NoConcatenate
Status_temp40:
Load *,
if([operation number]= [max operation number] and [confirmed qty position]>0 or [goods issued]>0
, [last OP],"next OP'S") as "concat OP temp"
Resident Status_temp30;
Drop Table Status_temp30;

NoConcatenate
Status_temp50:
Load
"%production order",
Concat(DISTINCT "concat OP temp", '/', [operation number]) as "concat OP"
Resident Status_temp40
group by "%production order";
Drop Table Status_temp40;

BR, Joanna