topic Re: How to count on fields that have changes in value. in QlikView

How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 08:43:45 GMT

Hi guys,

I have a requirement:

- display total no of records - i assume is count(record_ID) ?

- display records that have made changes of the total of number records?

I have attached a sample qvw with inline data.

Please help guys.

Rgds,

Jim

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 09:27:36 GMT

Can I used before() or after() ?

Re: How to count on fields that have changes in value.

robert_mika — Tue, 11 Oct 2016 10:06:35 GMT

Like this:

Record_2015:

LOAD * INLINE [

Year, Record_ID, Rating

2015, 001, 10

2015, 002, 20

2015, 003, 30

];

Changes:

Mapping Load

Record_ID&Rating as Rating2015,

Rating

resident Record_2015;

Record_2016:

load *,

if(Record_ID&Rating<>Record_ID&ApplyMap('Changes', Record_ID&Rating,0),1) as ChangesFlag;

LOAD * INLINE [

Year, Record_ID, Rating

2016, 001, 10

2016, 002, 10

2016, 003, 30

];

Re: How to count on fields that have changes in value.

antoniotiman — Tue, 11 Oct 2016 10:17:51 GMT

Hi Jim

may be

Count(DISTINCT {<Record_ID={'=Count(DISTINCT Rating) > 1'}>} Record_ID)

Regards,

Antonio

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 10:18:12 GMT

woww........ let me test it, let you know in a short while, Mika.

Rgds,

Jim

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 10:21:38 GMT

Mika,

I got synthetic key after loaded the script as below:

Record_2015:

LOAD * INLINE [

Year, Record_ID, Rating

2015, 001, 10

2015, 002, 20

2015, 003, 30

];

Changes:

Mapping Load

Record_ID&Rating as Rating2015,

Rating

resident Record_2015;

Record_2016:

load *,

if(Record_ID&Rating<>Record_ID&ApplyMap('Changes', Record_ID&Rating,0),1) as ChangesFlag;

LOAD * INLINE [

Year, Record_ID, Rating

2016, 001, 10

2016, 002, 10

2016, 003, 30

];

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 10:23:52 GMT

ok.. this works too, it gave me the no. of record changes too.

Re: How to count on fields that have changes in value.

sunny_talwar — Tue, 11 Oct 2016 10:32:43 GMT

Or may be one of these:

=Count(DISTINCT {<Record_ID = {"=Len(Trim(Only(Rating))) = 0"}>}Record_ID)

or this if you don't want to distinct count Record_ID

=Count({<Record_ID = {"=Len(Trim(Only(Rating))) = 0"}>}Record_ID)

Re: How to count on fields that have changes in value.

robert_mika — Tue, 11 Oct 2016 10:33:28 GMT

Missed the concatenate

Record_2015:

LOAD * INLINE [

Year, Record_ID, Rating

2015, 001, 10

2015, 002, 20

2015, 003, 30

];

Changes:

Mapping Load

Record_ID&Rating as Rating2015,

Rating

resident Record_2015;

Concatenate

Record_2016:

load *,

if(Record_ID&Rating<>Record_ID&ApplyMap('Changes', Record_ID&Rating,0),1) as ChangesFlag;

LOAD * INLINE [

Year, Record_ID, Rating

2016, 001, 10

2016, 002, 10

2016, 003, 30

];

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 10:43:01 GMT

Hi Sunny,

This works =Count(DISTINCT {<Record_ID = {"=Len(Trim(Only(Rating))) = 0"}>}Record_ID)

But how can i display the Record ID(that have changes) ? in the table box or text object

rgds,

Jim

Re: How to count on fields that have changes in value.

sunny_talwar — Tue, 11 Oct 2016 10:45:03 GMT

This will give you a comma separated list of Record_ID which changed

=Concat(DISTINCT {<Record_ID = {"=Len(Trim(Only(Rating))) = 0"}>}Record_ID, ', ')

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 10:50:15 GMT

wow.... Sunny! thanks!!! all works!

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 11:04:57 GMT

I have a question, Sunny.

I created a filter called - rating, so it has 10.20,30. so when i select 20. it wont show me any result....

why is it so?

Rgds

Jim

Re: How to count on fields that have changes in value.

sunny_talwar — Tue, 11 Oct 2016 11:09:47 GMT

The way the expression is structured, it gave you that result. May be try this:

=Concat(DISTINCT {<Record_ID = {"=Len(Trim(Only({<Rating, Record_ID = P(Record_ID)>}Rating))) = 0"}>}Record_ID)

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 11:14:30 GMT

Sunny Bro, for the Year...is it logical if i allow user to select filter Year?

Re: How to count on fields that have changes in value.

sunny_talwar — Tue, 11 Oct 2016 11:16:07 GMT

Is it logical? I guess it would depend on user needs, right? I am not sure what exactly are you looking for?

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 11:17:59 GMT

for instance, user access the dashboard, user clicks on year 2016 to see what record_id's rating has changed.

Re: How to count on fields that have changes in value.

sunny_talwar — Tue, 11 Oct 2016 11:18:43 GMT

Sure, that seems logical.

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 11:21:18 GMT

but i cant see it from here. is it bcos the record_id's has no changes in 2016?

Re: How to count on fields that have changes in value.

jim_chan — Tue, 11 Oct 2016 11:23:02 GMT