Count of ID's

[Qlikuser225]

Hello All,

Can you please hlep me with below issue.

I have table like

ID , Country , State

1, USA, NC

1, USA, NJ

1, CAN , T.O

2, CAN, TO

2, CAN, MN

2, USA , NY

3,  , WE

4, ,TY

4, ,TY

4, ,NH

How can i create table with following results.

ID , Data, State

1, USA(2) ; CAN(1) , NJ;NC;TO

2, CAN(2) ; USA(1), TO;MN;NY

3, Null ,   WE(1)

4 ,Null , TY(2);NH(1)

Please help

Thanks in Advance

[henrikalmen · Accepted solution]

I believe this should work, if your table is named "table". I haven't tried it though so I hope I haven't made too many mistakes... 

result:
load ID, concat(countrycounter, ';') as Data, States group by ID, States;
load ID, country&'('&counter&')' as countrycounter, States;
load ID, country, count(State) as counter, concat(distinct State,';') as States
resident [table]
group by ID, country;

(Note: in the result table I named the last column "States" instead of "State" so that there is no synthetic key created if you keep the original table and the resulting table in the data model.)

This may not give you exactly what you want for the null values, but try to start with this and see what you get from it and perhaps you'll be able to alter it for your specific needs.