topic Re: Creating a new field based on two fields in separate tables in QlikView

Creating a new field based on two fields in separate tables

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm trying to create a new field based on two other fields. The fields are Origin and Destination and I want a new field called Flow that looks like:

Origin&' <> '&Destination

when Origin comes before Destination in the alphabet or:

Destination&' <>'&Origin

if it doesn't.

I've been able to produce it as a calculated field in a table, but this causes the dashboard to slow down a lot so I'd like to create it in the Load statement instead. What's the best way to do this? The Origin and Destination fields are in two separate tables and whatever I try to write causes the Load script to fall over.

Thanks,

Ben

Re: Creating a new field based on two fields in separate tables

el_aprendiz111 — Tue, 21 Mar 2017 16:58:49 GMT

Hi,

Use JOIN CONCATENATE for the tables, then join the fields

Re: Creating a new field based on two fields in separate tables

aapurva09 — Wed, 22 Mar 2017 09:00:56 GMT

Hi,

You need to concatenate Origin and Destination columns in the script by joining or concatenating the two tables depending on the key.

Thanks

Re: Creating a new field based on two fields in separate tables

Wed, 22 Mar 2017 09:49:21 GMT

Hi,

You'll need to explain what I need to do in more detail than that for me to understand it, my knowledge of QlikView is very basic

If you assume the tables are ORIGIN2 and DEST2 with the fields that I need to include called ORIGIN and DEST, how would I JOIN the two fields.

Thanks,

Ben

Re: Creating a new field based on two fields in separate tables

Wed, 22 Mar 2017 09:50:55 GMT

Hi,

If you could provide more detail on how I would do that it would be appreciated - I've explained the issue in more detail in my reply above.

Thanks,

Ben

Re: Creating a new field based on two fields in separate tables

sasiparupudi1 — Wed, 22 Mar 2017 12:14:03 GMT

Create a mapping table between the Key field and the origin table

In the destination table, call the above map using applymap function to load the origin filed into it.

Once you have the fields available in the target table, you could apply your if condition to create the required field

hth
Sas

Re: Creating a new field based on two fields in separate tables

ahaahaaha — Wed, 22 Mar 2017 13:15:35 GMT

Hi Ben,

Can we see a small fragment of your tables with fields so that my colleagues and I can more specifically help you?

Regards,

Andrey

Re: Creating a new field based on two fields in separate tables

Fri, 31 Mar 2017 13:47:22 GMT

Hi Andrey,

Here's a very small example of what I'm trying to do. I've calculated a field called Flow, but ideally I'd like to do this in the loading script as using the calculated field can be slow with the actual amount of data it uses.

Thanks,

Ben