topic How do I compare fields from 2 separate load statements? in QlikView

How do I compare fields from 2 separate load statements?

Wed, 04 Mar 2015 12:50:14 GMT

I am loading data from one file to another and joining it together as the load script below shows. The goal is to validate that my update to a system worked. The my_list table has what names in the system should have been updated to. The system table loads a dump from the system to show what the name currently is. I want to create a field called update_success (bolded below) that contains 'Yes' if what the name should be matches the name in the system, and 'No' if it does not match. However, when I load the script below I get the error "Field not found - <name_should_be>". Is there a better / good way to make this work? Thanks.

my_list:
LOAD text(id_num) as id_num,
name_should_be
FROM

(ooxml, embedded labels, table is data);

system:
left join (my_list)
LOAD text(id_num) as id_num,
name as name_in_system,
if(name_should_be = name_in_system, 'Yes', 'No') as update_success
FROM
C:\Downloads\dump.txt
(txt, codepage is 1252, embedded labels, delimiter is '~');

Re: How do I compare fields from 2 separate load statements?

PrashantSangle — Wed, 04 Mar 2015 12:56:04 GMT

Hi,

Try after left join

Try like

my_list:
LOAD text(id_num) as id_num,
name_should_be
FROM

(ooxml, embedded labels, table is data);

//system:
left join (my_list)
LOAD text(id_num) as id_num,
name as name_in_system
//if(name_should_be = name_in_system, 'Yes', 'No') as update_success
FROM
C:\Downloads\dump.txt
(txt, codepage is 1252, embedded labels, delimiter is '~');

My_List_System:

load *,

if(name_should_be = name_in_system, 'Yes', 'No') as update_success

Resident my_list;

Regards

Re: How do I compare fields from 2 separate load statements?

PrashantSangle — Wed, 04 Mar 2015 12:58:26 GMT

Hi,

Before combining two table you can not my_list table fields into System table.

First you have to join it then using resident load you can access both field in one table.

Regards

Re: How do I compare fields from 2 separate load statements?

Wed, 04 Mar 2015 13:01:54 GMT

Thanks Max. This worked.