Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
nlamprecht
New Contributor III

Is one value in a column present in another column

Hi everyone! Thank you so much for taking the time to read my question and potentially help me out!

I am currently creating a dashboard where I am looking to see if one value is present in two columns.  If it is not present in the other column, I will then need to look to see how many times it occurs and if it is above a certain amount.  Please see an example below:

** So I only want have a chart that pulls back week_2 colours that were NOT in week1_colour, and if they were not listed in week1_colour, then have a value of week2_sales ABOVE 5.

Week1_colour          week1_sales               week 2_colour             week2_sales

blue                              3                                  red                                   10

red                                6                                  burgundy                         15

green                            7                                  yellow                              3

purple                          10                                 lilac                                   9

yellow                          25                                 pink                                   1

aqua                            16                                 orange                              6

So I want a table that (based on what's above), would result with:

week2_colour           week2_sales

lilac                                   9

burgundy                         15

orange                              6

** not result with blue, red, green, purple, aqua as either only present in week1 OR have a sales value that is not over 5.

What would the expression be that I plug into the chart/how would I be able to go about doing this?

Many thanks again!

1 Solution

Accepted Solutions

Re: Is one value in a column present in another column

May be something like this:

Dimension

week 2_colour

Expression

Sum({<[week 2_colour] = e(Week1_colour)>} week2_sales)

View solution in original post

3 Replies
MVP
MVP

Re: Is one value in a column present in another column

It would be better if you provide source data or sample application.

Re: Is one value in a column present in another column

May be something like this:

Dimension

week 2_colour

Expression

Sum({<[week 2_colour] = e(Week1_colour)>} week2_sales)

View solution in original post

nicolas66
New Contributor III

Re: Is one value in a column present in another column

Bonsoir,

may be:

T1:

LOAD Week1_colou,

     week1_sales

FROM

(ooxml, embedded labels, table is Feuil1);

T2:

load*

where week2_sales>5

;

LOAD [week 2_colour],

     week2_sales

FROM

(ooxml, embedded labels, table is Feuil2)

where not Exists (Week1_colou,[week 2_colour])

;

drop table T1;