topic compare 2 Fields ? in QlikView

compare 2 Fields ?

Mon, 10 May 2010 18:42:36 GMT

Hello Together,

I am slowly go crazy !
I have two fields x and y with these values:

Field x = {a, b, c, d, e,f,g}

Field y = {b, c, d}.

I would like to generate a new field z from these both fields where only the values are included which seem in both fields x, y.

Then this would be a new field z = {b, c, d}.

How have i code this in script?

Thank you very much!

compare 2 Fields ?

Mon, 10 May 2010 19:38:21 GMT

hi,

Here a script which create a new table with a field z which contains the duplicate value

table:
LOAD * INLINE [
x
a
b
c
d
e
f
];

Concatenate LOAD * INLINE [
x
b
c
d
];

compte:
Load x , count(x) as n
resident table
group by x ;

Duplicate:
Load x as z
resident compte
where n > 1 ;

compare 2 Fields ?

johnw — Mon, 10 May 2010 19:48:39 GMT

TableZ:
LOAD fieldvalue('X',iterno()) as Z
AUTOGENERATE 1
WHILE len(fieldvalue('X',iterno()))
;
INNER JOIN (TableZ)
LOAD fieldvalue('Y',iterno()) as Z
AUTOGENERATE 1
WHILE len(fieldvalue('Y',iterno()))
;

compare 2 Fields ?

Mon, 10 May 2010 19:59:40 GMT

Hi John Witherspoon,

you are my hero 😉

Thank you very match

compare 2 Fields ?

Mon, 10 May 2010 20:18:38 GMT

Hi J.W,

Once more thank you very much!

If you can also show me how I provide a key in the generated z table.
I thought of something like that:

z as key1
and in another field z2 as key1.

Over again thank you very much!

Many greetings,
honour

compare 2 Fields ?

johnw — Mon, 10 May 2010 20:22:05 GMT

Sorry, I don't understand what you're asking for.

compare 2 Fields ?

Mon, 10 May 2010 20:33:07 GMT

sorry my fault,

i have wrong expressed myself.
But, in the meantime, I have understood it.

Many thanks once more!

compare 2 Fields ?

Thu, 17 Jun 2010 10:21:50 GMT

I have a table named Customers, there is a column in it named Code. The thing that i want to do is match the column of the table in Match() function. We mostly do as follows:

TempCustomers:

Load

Code,

If (Match(Code, '1','3','2','4','5','6','7','8','9','10') as XYZ

Resident Customers;

What I need is i want to have all values passed from match write a Load Statement where it is written above in If (Match(Code, Load Statement) as XYZ or If (Match(Code, Customers) as XYZ

My senior says its possible, I dont think so it is? I dont know what the hell he is talking about.

Regards,

Khalid

compare 2 Fields ?

johnw — Thu, 17 Jun 2010 18:16:07 GMT

To see if I understand what you want...

You're loading a list of codes in a load statement. Then in another load statement, if the code for that row is in the previously-loaded list, you want to assign one value, like 'in list', and if not, another value, like 'not in list'. Is that correct? So you want something like this?

[Code List]:
LOAD * INLINE [
Code
1
2
3
4
];
[Real Table]:
LOAD
Something
,SomethingElse
,if(Code is in the Code List table,'in list','not in list') as XYZ
...
;

If so, then yes, I'm already thinking of two solutions, both pretty simple. Neither uses match(), though. Oh, now I'm thinking of a third solution using match(), but it's signifiantly more complicated than the other two. However, if your senior is telling you that it's possible, but NOT telling you how to do it, does he or she want you to discover a solution for yourself? Or should I just tell you the solutions I'm thinking of?

Edit: Posted five or six solutions in the other thread where you posted essentially the same question:

http://community.qlik.com/forums/p/27167/117122.aspx#117122