if exists qlik sense

[pascos88]

Good morning I have a question.

I have to create a table like this:

Aspect           dimension      Aspect ID          2017     2016     2015         2014

     X                    Y                    125               Yes          No        No          Yes

Where the columns of the years will contain yes, is the aspect Id exist for that years. 

In the case above the aspectID 125 exists for the year 2017 and 2014.

The problem is that in my db I have just two rows for this aspect ID that are

AspectID           Year

125                    2017    

125                    2014.

How I can fill this rows with no if are missing in my db?

I am just creating a normal table and I tried this without any results:

= if(max({< Year = {"2017"}>} Year) <> 0 ,'Yes', 'No')

Any helps?

Thanks

[YoussefBelloum]

Hi,

you have 2 options here, 1 on the back-end and 1 on the front end:

Back-end:

on your load, add this:

if(len(trim([Aspect ID]))<>0, 'yes','no') as STATE_ID //for example

after that, you will have to use a pivot table, use Year and STE_ID fields as dimensions and pivot the dimension Year, you will have the exact same expected output.

Front-end:

on the expression on the table chart, put this:

=Only(If(len(trim([Aspect ID]))<>0,'yes','no'))

[pascos88]

HI,

thanks for your answere

I would use in the FE.

I need a set expression in for each column, I need to specify the years (2017, 2016, 2015...)

Do you know how I can update the script?

[YoussefBelloum]

EDITED

Yes you can use a set analysis instead of the described above, what is your expression ?

Also, I think you should pivot the Year Dimension to have your table like this.

Can you attach a sample QVW or sample data ?