Count and generate new Columns

[psk180590]

Hello All,

I have my sample data and expected result in two different tabs of the attached excel sheet. Please, have a look at the data to understand my problem better.

An basic overview: I have few IDs generated randomly and i'm looking to generate the Count of IDs per Region, State and Country.

Please Note: I need the Count not Sum.

The method i tried is:

1. Read all the Sample data, generate Count(IDs) as IDBasis and store in Basis.qvd.

2. Read from Basis.qvd. Without the Region, do a Sum(IDBasis) as IDState and store as Region.qvd

3. Read from Region.qvd. Wothout the State, do a Sum(IDState) as IDCountry and store as State.qvd

4. Do a left Join and read all the three qvd files into a Datamodell

But, i always end up getting the same count for each type.

No selection all the three Values are same. If state selected all the three values change where as IDCountry shouldn't samething when a region is selected both the IDState and IDCountry change.

Thanks in Advance.

Wishing you all a Happy and Safe Diwali 

[florentina_doga · Accepted solution]

pls see attach

[felipedl]

Hi Pakalapati,

The following script should give you the result:

Data:

LOAD Country,

     State,

     Region,

     ID

FROM

(ooxml, embedded labels, table is Data);

RegionCount:

Load

Region,

Country,

    State,

Count(Region) as RegionCount

Resident Data

Group by Country,

     State,

     Region;

StateCount:

Load

Country,

    State,

Count(State) as [State Count]

Resident Data

Group  by Country,

     State;

IDCount:

Load

count(ID) as [Total Count]

Resident Data;

left join(Data)

Load

Region,

Country,

    State,

RegionCount

Resident RegionCount;

left join(Data)

Load

Country,

State,

[State Count]

Resident StateCount;

join (Data)

Load

[Total Count]

Resident IDCount;

drop tables RegionCount,StateCount,IDCount;

Giving the following table:

[effinty2112]

Hi Pakalapati,

Try:

Data:

LOAD Country,

     State,

     Region,

     ID

FROM

[..\Sample Data.xlsx]

(ooxml, embedded labels, table is Data);

Left Join(Data)

LOAD

Region,

count(ID) as [Region Count]

Resident Data Group by Region;

Left Join(Data)

LOAD

State,

count(ID) as [State Count]

Resident Data Group by State;

Left Join(Data)

LOAD

count(ID) as [Total Count]

Resident Data;

to get:

Country	State	Region	ID	Region Count	State Count	Total Count
IND	AP	AM	DE4	1	10	25
IND	AP	AN	DD5	1	10	25
IND	AP	GU	HJ8	1	10	25
IND	AP	KL	IJ8	1	10	25
IND	AP	ON	JH6	1	10	25
IND	AP	PO	LK8	1	10	25
IND	AP	SK	GG5	1	10	25
IND	AP	TP	FR5	1	10	25
IND	AP	VJ	AA2	1	10	25
IND	AP	VZ	JK7	1	10	25
IND	MH	AU	HZ6	1	7	25
IND	MH	MU	A34	2	7	25
IND	MH	MU	GH5	2	7	25
IND	MH	NP	AD6	1	7	25
IND	MH	PU	A23	2	7	25
IND	MH	PU	FG4	2	7	25
IND	MH	SH	AF4	1	7	25
IND	TG	AD	TH6	1	8	25
IND	TG	HY	KI8	1	8	25
IND	TG	KM	ZH6	1	8	25
IND	TG	MK	ZT6	1	8	25
IND	TG	NZ	WE6	1	8	25
IND	TG	RR	RE3	1	8	25
IND	TG	SC	TZ5	1	8	25
IND	TG	WR	OL9	1	8	25

Kind regards

Andrew