Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

imrasyed
Contributor

Where condition failing

Hi Experts,

I have two tables.Joined them and created a third table using Resident for getting the data based on the condition of two fields coming from two  tables.

TableA:

X,Y

TableB:

X,Z

TableC:

X,Y,Z

resident TableA

where Y <=Z;

so this where condition is not working?

Where am I going wrong?

Appreciate u r help.

1 Solution

Accepted Solutions
MVP
MVP

Re: Where condition failing

Try this - Basically don't join on C1yr... just do a Cartesian join

Temp:

LOAD * INLINE [

    id, Customer, Sales, Clyr, Clyr1

    1, A, 100, 2012, 2012

    2, B, 200, 2013, 2013

    3, A, 300, 2013, 2013

    4, C, 150, 2014, 2014

    5, D, 200, 2015, 2015

    6, B, 140, 2016, 2016

    7, E, 400, 2017, 2017

    8, g, 200, 2013, 2013

    9, C, 340, 2018, 2018

    10, F, 600, 2019, 2019

];



Join(Temp)

LOAD MCl,

MClyr;

LOAD * INLINE [

    Clyr, MCl, MClyr

    2018, 2018, 03

];


NoConcatenate

ts:

LOAD *

Resident Temp

Where Clyr1 <= MCl;


DROP Table Temp;

10 Replies
MVP
MVP

Re: Where condition failing

Join TableA and TableB so that you can perform the condition in where statement

TableA:

X,Y

Join (TableA)

X,Z

TableC:

X,Y,Z

resident TableA

where Y <=Z;

imrasyed
Contributor

Re: Where condition failing

Yes Sunny I tried that as well but still its the same issue.

In Y column I have year from 2012 to 2019 and in Z I have  Year 2018.

So acccording to the condition it should fetch 2012 to 2018  instead it is fetching only  for Yr2018 

jolivares
Valued Contributor

Re: Where condition failing

So well I don't understand your problem... could you post a sample table to see what happen to you, cause I agree with Sunny.  This is a simple case to solve with a Join

imrasyed
Contributor

Re: Where condition failing

Hi Juan,


Here is the sample data.


Temp:

Load * Inline

[

    id, Customer, Sales,Clyr,Clyr1


     1,A, 100,2012,2012


     2,B, 200,2013,2013

    

     3,A, 300,2013,2013


     4,C, 150,2014,2014


     5,D, 200,2015,2015


     6,B, 140,2016,2016


     7,E, 400,2017,2017

     8,g,200,2013,2013

     9,C, 340,2018,2018

     10,F,600,2019,2019

];


outer Join(Temp)

Final:

Load * Inline

[

    Clyr,MCl,MClyr


    2018,2018,03

   

     ];



NoConcatenate


ts:

load *

Resident Temp

where Clyr1 <= MCl;



drop Table Temp;

Here I am getting only yr 2018 data.Instead I want to get  2012 to 2018.

Please can you suggest me where i am going wrong.

MVP
MVP

Re: Where condition failing

Try this - Basically don't join on C1yr... just do a Cartesian join

Temp:

LOAD * INLINE [

    id, Customer, Sales, Clyr, Clyr1

    1, A, 100, 2012, 2012

    2, B, 200, 2013, 2013

    3, A, 300, 2013, 2013

    4, C, 150, 2014, 2014

    5, D, 200, 2015, 2015

    6, B, 140, 2016, 2016

    7, E, 400, 2017, 2017

    8, g, 200, 2013, 2013

    9, C, 340, 2018, 2018

    10, F, 600, 2019, 2019

];



Join(Temp)

LOAD MCl,

MClyr;

LOAD * INLINE [

    Clyr, MCl, MClyr

    2018, 2018, 03

];


NoConcatenate

ts:

LOAD *

Resident Temp

Where Clyr1 <= MCl;


DROP Table Temp;

imrasyed
Contributor

Re: Where condition failing

Thanks a lot Sunny,

But only concern whether is it going to be a performance issue?

MVP
MVP

Re: Where condition failing

Depending on the size of the two tables, your concerns are valid... It might help to know what exactly are you trying to do as there might be another easier way to do it

imrasyed
Contributor

Re: Where condition failing

I have first table with 2225 records and the second table with  1 record.

Actually I have 'Time'  table with dates from 2012 to 2019 and the other table 'Month End' which has the latest date.So I am getting the data based on the 'Month End'  column.

MVP
MVP

Re: Where condition failing

Joining any table with another table which have 1 record should give you no problem whatsoever.... there might be alternatives using FieldValue() function... but I am not really sure I understand this well enough to give you a script for it

Actually I have 'Time'  table with dates from 2012 to 2019 and the other table 'Month End' which has the latest date.So I am getting the data based on the 'Month End'  column.