How to count on fields that have changes in value.

[jim_chan]

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

[jim_chan]

Can I used before() or after() ?

[robert_mika]

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

];

[antoniotiman]

Hi Jim

may be

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

Regards,

Antonio

[jim_chan]

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

Rgds,

Jim

[jim_chan]

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]

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

[sunny_talwar]

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)

[robert_mika · Accepted solution]

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]

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