table fail to work

[#1]

Dear All,

I have 2 table like these:

Nov-2016:

date	id	grade
30-Nov-2016	001	1
30-Nov-2016	002	10
30-Nov-2016	003	5

Dec-2016

date	id	grade
31-Dec-2016	001	1
31-Dec-2016	002	9
31-Dec-2016	004	6

I want to make a table like this:

result table:

id	30-Nov-2016	31-Dec-2016
002	10	9

The logic of result table is if one of the id have grade 10 in either month, it will show that id and it's grade in both month.

I tried many different way but it doesn't work.

Please help, thank you so much.

[sunny_talwar]

May be this

Dimensions

id,

date

Expression

Only({<id = {"=Max(Grade) >= 10"}>} Grade)

[Sergey_Shuklin]

Hello, Jason!

When you load your tables use one more Load statement to get mark on ids with grade=10:

t1:

LOAD date,

    id,

    grade

FROM

[https://community.qlik.com/thread/266959]

(html, codepage is 1251, embedded labels, table is @1);

t2:

LOAD date,

    id,

    grade

FROM

[https://community.qlik.com/thread/266959]

(html, codepage is 1251, embedded labels, table is @2);

Left Join (t1)

LOAD Distinct

id,

1 as mark

Resident t1

Where grade=10;

After that you'll get such output table:

And all you'll have to do is to create a pivot table with all ids which is marked as 1.

P.S.: If you have multiple results of grade for current id then Only() function will return Null() and you'll have to use something else like Sum(), Max() and so on.

Hope it's what you are lookin for.