Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Comparing 2 Values from 2 Tables??

Hi guys ive got these 2 tables:

Table1 Expression= Sum (If(Report='current',$(var_sales)))

Header 1Header 2Header 3Header 4Header 5

City

week1week2week3week4

Paris

3424423442243423242
London-4234423422342
New York34244234423442342
Lisbon423424234244322434

Table2 Expression = Logic (just a column name)

Header 1Header 2Header 3Header 4
Tier1week1week2week3

Paris

YESYESYES
LondonYESYESYES
New YorkYESYESYES
LisbonYESYESYES

Now im trying to insert a rule for the background colouring on the Expression for Table2, what this rule will do is simple but i cant get it to work.

The rull will do is, IF there is a "Yes" on Table2 table AND a NULL value on the Table1 it will color it Red, now i got everything working expect i cant get the verification on the Table1 , the expresssion on that table is "Sum (If(Report='current',$(var_sales)))", what i had was

if(Logic='YES' and isnull(?????), RGB(255,0,0),if (Logic='NO' and ?????<> Null,RGB(255,0,0),RGB(0,255,0)

What it does completely is, if its YES and theres a correspondent value on the other table, then it checks correct and highlights green, now if there is a NO and there is a null on the correspondent table it still checks correct, IT will only Highlight red when, theres is a Yes that corresponds to NUll, or a No that corresponds to a Value, the Formula is not hard, i just dont know what Function to use to tell it to check Table1.

Many Thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: Comparing 2 Values from 2 Tables??

I'm not sure what you mean by "check table 1".  You can't actually refer to values in a different chart.  However, since it appears your dimensions are exactly the same, you can just recalculate them in table 2.  I also don't recommend using isnull() ever, because it doesn't work correctly in some versions of QlikView.  Use len() instead, which seems to work in all versions I've encountered.  So something like this, perhaps:

if(Logic='YES' and len(sum(if(Report='current',$(var_sales))))=0,rgb(255,0,0)
,if(Logic='NO' and len(sum(if(Report='current',$(var_sales))))>0,rgb(255,0,0)
                                                                ,rgb(0,255,0)

Or to avoid doing the sum() twice:

if(len(sum(if(Report='current',$(var_sales))))
  ,if(Logic='NO' ,rgb(255,0,0),rgb(0,255,0))
  ,if(Logic='YES',rgb(255,0,0),rgb(0,255,0))

1 Reply
MVP
MVP

Re: Comparing 2 Values from 2 Tables??

I'm not sure what you mean by "check table 1".  You can't actually refer to values in a different chart.  However, since it appears your dimensions are exactly the same, you can just recalculate them in table 2.  I also don't recommend using isnull() ever, because it doesn't work correctly in some versions of QlikView.  Use len() instead, which seems to work in all versions I've encountered.  So something like this, perhaps:

if(Logic='YES' and len(sum(if(Report='current',$(var_sales))))=0,rgb(255,0,0)
,if(Logic='NO' and len(sum(if(Report='current',$(var_sales))))>0,rgb(255,0,0)
                                                                ,rgb(0,255,0)

Or to avoid doing the sum() twice:

if(len(sum(if(Report='current',$(var_sales))))
  ,if(Logic='NO' ,rgb(255,0,0),rgb(0,255,0))
  ,if(Logic='YES',rgb(255,0,0),rgb(0,255,0))