Script to find number of people changed their country

[#1]

Suppose i have 10 people in which 5 people have change their country from INDIA to US in the list.I want a script to find out how many people change their county.Thanks in advance.

[alexandros17]

This is the easiest way to do what you asked, for a more complex situation you have to add a numeration to each change.

hope it helps

[fdelacal]

PLEASE SE ATACHA QVW.

LOAD Name,

    if( upper(Country) = 'INDIA',Country) AS CUNTRY_INDIA,

        if( upper(Country) = 'US',Country) AS CUNTRY_USA,

        Country

        FROM

C:\Data.xlsx

(ooxml, embedded labels, table is Sheet1);

HOPE IT HELPS YOU.

[#4]

Cannot open file 'C:\Data.xlsx' The system cannot find the file specified.Error is coming while executing the script.and as far as i think it will tell how many peoples are from india and how many are from US.But actually my requirement is how many people changed their country.if u can see the attached file Asit has changed from INDIA to US like that in total 5 people has changed. so i want how many people changed???

[#5]

Thanks for ur reply.as i m using the personal edition so cant open ur qvw file.so can u please post the script file???

[fdelacal]

C:\Data.xlsx you have to change to the rout of your xlsx

try with this.

LOAD

        count(if( upper(Country) = 'US',Country)) AS NUm_change,

        Country

        FROM

change it //        C:\Data.xlsx

(ooxml, embedded labels, table is Sheet1) Group by Country;

[vincent_ardiet]

Hi,

In an expression (no script), you can use this :

=sum(if(aggr(count(Country),Name)>1,1))

Regards,

Vincent

[alexandros17]

Hi asitmishra879

I just loaded Data and set a straight chart with Name as dimension and the following expression:

if(Count(Country)>1,1,0)

This expression just solve the easy problem but doesn't solve more complex situation.

For complex situations you need more information on the "history" of customer changes ... so you must add at least a column to your data that explain the first, the second and all others changes of nations.

[#9]

See the below figure..the Num _change is coming as 0 and 8.but the actual number of people who has changed country is 5.

iiiiiii

[fdelacal]

in the excel you give me i have 8 with us and 7 with india.

see image atach

if you want to count india do the same,

but is more easy do it in design.

Thanks