Combine two fields into one (temp fields for Where Not Exists)

[kdaniels-obrien]

I am doing an incremental load from a web service. In my load script, I use a where not exists clause.  In order for me to concatenate the new data from the web service with the data stored in a qvd, I use a temp field when loading from the web service. Unfortunately, this creates two fields: tempOrderNum and OrderNum.  On rows where I have a tempOrderNumValue, my OrderNum is null.  This means my new data is missing from my UI.  How can I specify in the load script something like: if OrderNum is null, replace the null value with tempOrderNum? I have attach some snippets of code and a table displaying the problem.  I did see a post similar to mine question but the solution was a .qvw file which I can not open in Qlik Sense. Here is the link to the other problem for reference: https://community.qlik.com/t5/QlikView-App-Development/Combine-Two-Fields-As-One/m-p/468059 

Note: I load my new data from the web service right before the code seen in the screenshot into a table. In that table, I alias OrderNum as tempOrderNum. I also have a field that gives me the row version from SQL (this acts as my unique ID field)  

What I get is..  (this is dummy data)

 

I need the tempOrderNum 'BG1011' to replace the null value for row version 18687478. 

Any help is appreciated! thanks in advance!

[MayilVahanan]

HI @kdaniels-obrien 

Try like below

Concatenate

Load *, tempOrderNum as OrderNum 

from [ursource] where not exists(tempOrderNum , OrderNum);

If its works fine, you can drop tempOrderNum  field also.

[kdaniels-obrien]

I have given that a try but I get an error, "Field must be unique within the table" (I have OrderNum in the QVD table as well) 

[MayilVahanan]

Hi @kdaniels-obrien 

Instead of using *, u can list the field name and remove the ordernum and rename the tempordernum as ordernum .

Might be, it helps you

[kdaniels-obrien]

When I try that, it says "tempOrderNum not found".  I have a table right before the Concatenate Load * that requests new data from my web service and in this table, I alias OrderNum as tempOrderNum. Do I need to create a new table to get this to work?