Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
jim_chan
Contributor III

How to count on fields that have changes in value.

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

Tags (2)
1 Solution

Accepted Solutions

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

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

];

24 Replies
jim_chan
Contributor III

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

Can I used before() or after() ?

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

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

];

Capture.PNG

antoniotiman
Honored Contributor III

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

Hi Jim

may be

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

Regards,

Antonio

jim_chan
Contributor III

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

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

Rgds,

Jim

jim_chan
Contributor III

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

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

];

jim_chan
Contributor III

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

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

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

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.

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

];

jim_chan
Contributor III

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

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