Count of ID which got 'OS' rating for two consecutive years ...

[shekhar_analyti]

Hi All ,

How to show Count of ID which got 'OS' rating for two consecutive years in a Text Box ?

How to show Count of ID which never got 'OS' rating in a Text Box ?

Thanks

Shekar

[Anil_Babu_Samineni]

Are you expecting 27? May be this

=Count({1<Rating-={'OS'}>}ID)

[shekhar_analyti]

Hi Anil ,

I don't think that will give correct values .

There are total of 11 distinct ID .

1)  And I am trying to show count of those who never got OS in one Text Box  -- there are 8 ID who never got OS ...

and

2)  Count of ID which got 'OS' rating for two consecutive years in another Text Box

[shekhar_analyti]

Find the attached data .

[effinty2112]

Hi Shektar,

One of your requests is easy enough

count of those who never got OS in one Text Box

=count({$<ID = E({<Rating={'OS'}>})>}DISTINCT ID)

The other can be done in the load script, is a scripting solution okay for you?

regards

Andrew

[tresesco]

1) =Count({<ID=e({<Rating={'OS'}>})>}distinct ID)

2) You have to work on the script a bit like:

t1:

LOAD Year&ID as Key,

  ID,

    First_Name,

    Last_Name,

    Year,

    Gender,

    Rating

FROM

(ooxml, embedded labels, table is Sheet1);

Left join

Load

  Year+1&ID as Key,

1 as Flag

Resident t1;

Then front-end expression like:

=Count({<Flag={1}>}distinct ID)

[shekhar_analyti]

Hi Andrew ,

Thanks for reply .

Actually  requirement is to find the count of the other part on the fly , without scripting .

[effinty2112]

Hi,

In script

[From xlsx]:

LOAD ID,

     First_Name,

     Last_Name,

     Year,

     Gender,

     Rating

FROM

Ratings.xlsx

(ooxml, embedded labels, table is Sheet1);

Flag:

Load

ID,

Year,

Rating,

If(ID = Peek(ID)  AND Year = Peek(Year)+1 AND Rating = 'OS' AND Peek(Rating) = 'OS', 1,0) as Flag

Resident [From xlsx] Order by ID,Year;

Left Join ([From xlsx])

LOAD * Resident Flag;

Drop Table Flag;

Now a textbox with count({$<Flag = {1}>}Distinct ID) returns "1".

cheers

Andrew

[effinty2112]

Hi Shekhar,

                       Good luck with that! Maybe you could have a chart give you the answer using inter record functions but to get the answer in a textbox is non-trivial.

cheers

Andrew

[shekhar_analyti]

stalwar1‌ .. can above be achieved without changing existing load script ..