topic Re: Comparing field Data in QlikView

Comparing field Data

Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

Question. I have data from two different resources which I have linked together using a concatenated key:

article+color+size+amount as article_key.

This is loaded into the table differentiating them using a custom Table field. (one is loaded as 'table1' as Table, the other 'table2' as Table).

The list looks as follows:

article_key	Table
trouser-900-34-20	table1
trouser-900-34-20	table 2
sweater-999-XL-10	table1
sweater-999-XL-15	table2

What I want to do know is to create a statement in which the sweater-999-XL-15 gives a different color since it is only available in table2 and not in table1. Is it possible to compare data like this?

Please let me know.

Kind regards,
Maarten

Re: Comparing field Data

sunny_talwar — Wed, 29 Jul 2015 12:30:39 GMT

May be create a flag for the entry in the script and use that to color it:

Table:

LOAD * Inline [

article_key, Table

trouser-900-34-20, table1

trouser-900-34-20, table2

sweater-999-XL-10, table1

sweater-999-XL-15, table2

];

Join(Table)

LOAD If(article_key = Peek('article_key'), 1, 0) as Flag,

article_key,

Table

Resident Table

Order By article_key, Table;

Output:

Re: Comparing field Data

Thu, 30 Jul 2015 09:05:40 GMT

Hi!

Thanks for your reply, but it doesn't seem to be the answer.

Re: Comparing field Data

sunny_talwar — Thu, 30 Jul 2015 10:20:50 GMT

You want to color articles in table 2 which do not have a matching article in table1, right??? At least that's how I interpreted your situation. What are you trying to do, if above is not what you want???

Re: Comparing field Data

rubenmarin — Thu, 30 Jul 2015 10:59:24 GMT

Hi Maarten, if that is an expression in a table you can set the font color like:

If(Count(DISTINCT Table)<2 and Table<>'table1', Red(), Black())

Re: Comparing field Data

maxgro — Sat, 01 Aug 2015 19:18:47 GMT

in a chart with article_key as dim you can use the following expression

if(Only(Table)='table2',red())

Re: Comparing field Data

qlikviewwizard — Sun, 02 Aug 2015 06:25:44 GMT

Hi,

Can you please upload your sample file to demonstrate your issue?

Thank you.

Re: Comparing field Data

Tue, 04 Aug 2015 13:40:04 GMT

This is a great answer. It works and immediately visualizes the results. Thank you!

Re: Comparing field Data

Thu, 06 Aug 2015 12:17:48 GMT

Im now trying to add this to the script for calculations. Is this possible. Im getting different results.

My script looks like:

NoConcatenate

DATA:

LOAD

Article_Key,

Concat(DISTINCT Table, ' & ') as TABLE_FLAG

Group By KEY_ARTIKEL;

Or is there a way to calculate results for 'table 1' , 'table 1 & table 2' or 'table 2' seperately?

cheers,

Maarten