topic Re: Combine two fields into one (temp fields for Where Not Exists) in App Development

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

kdaniels-obrien — Sat, 16 Nov 2024 17:42:31 GMT

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!

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

MayilVahanan — Tue, 15 Dec 2020 01:48:21 GMT

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.

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

kdaniels-obrien — Tue, 15 Dec 2020 02:22:54 GMT

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)

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

MayilVahanan — Tue, 15 Dec 2020 03:06:58 GMT

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

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

kdaniels-obrien — Tue, 15 Dec 2020 13:26:21 GMT

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?