topic Re: table fail to work in QlikView

table fail to work

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: table fail to work

sunny_talwar — Sun, 09 Jul 2017 04:50:12 GMT

May be this

Dimensions

id,

date

Expression

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

Re: table fail to work

Sergey_Shuklin — Sun, 09 Jul 2017 06:17:41 GMT

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.