Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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