Display data along with keyword count

[Anonymous]

I have the below data (Table 1) in EXCEL

The identifier column indicate whether row is country or city.The Data columns indicates the data and comments indicates the Comments

Identifier	Data	Comments
Country	Japan	comments Japan
City	Tokyo	comments
Country	Spain	comment
City	Madrid	City Madrid
Country	France	France country Paris City
City	Paris	City Paris

I want to display data in below format

Where the based on identifier the data column is displayed in Country,City and comments column.

If the country name found in Comments then 1 is displayed in Country count column.

If the city name found in Comments then 1 is displayed in City count column.

Country	City	Comments_Country	Comments City	Country Count	City count
Japan	Tokyo	comments Japan	comments Tokyo comments	1	0
Spain	Madrid	comment	City Madrid	0	1
France	Paris	France country Paris City	City Paris	1	1
			Total	2	2

Please Suggest

[Anil_Babu_Samineni]

Not sure, Why 0 came for Spain Country and Tokyo City?? can you help us to understand the highlighted one. FYI, Here we are

[Anil_Babu_Samineni]

And for Country Count, I believe we are going to exclude comment. Not sure, From Tokyo

Used Count({<Comments -= {'comment'}>} Identifier)

[woshua5550]

Hi

you want to deal with it in ETL prossess or in frontend ?

[Anonymous]

Data format needs to be done in script and count can be done in script or displaying the data.

[antoniotiman]

May be like this

Temp:
LOAD AutoNumber(RowNo(),Identifier) as A,* Inline [
Identifier, Data, Comments
Country, Japan, comments Japan
City, Tokyo, comments
Country, Spain, comment
City, Madrid, City Madrid
Country, France, France country Paris City
City, Paris, City Paris
];
Table:
LOAD A,Data as Country,Comments as Comments_Country
Resident Temp
Where Identifier = 'Country';
Left Join (Table)
LOAD A,Data as City,Comments as Comments_City
Resident Temp
Where Identifier = 'City';

[Anil_Babu_Samineni]

I've done same script to get output as 1st image, But looks like he is getting some zeros that part not done

[antoniotiman]

If You have sequence like this

Country

City

City

Country

City

City

City

...

then change to

Temp1:
LOAD AutoNumber(RowNo(),Identifier) as A,* Inline [
Identifier, Data, Comments
Country, Japan, comments Japan
City, Tokyo, comments
City, Tokyo1, comments
Country, Spain, comment
City, Madrid, City Madrid
Country, France, France country Paris City
City, Paris, City Paris];
Temp:
LOAD *,If(Identifier='City',Peek(B),A) as B
Resident Temp1;
Table:
LOAD A,B,Data as Country,Comments as Comments_Country
Resident Temp
Where Identifier = 'Country';
Left Join (Table)
LOAD B as A,Data as City,Comments as Comments_City
Resident Temp
Where Identifier = 'City';

[Anonymous]

It will be fine for me for below output (which I am trying to get using "Table" table from above script)

Country	City	Comments_Country	County Count	City Count
Japan	Tokyo	comments Japan	1	0
Spain	Madrid	comment	0	0
France	Paris	France country Paris City	1	1

- If the country found in Comments_County column then 1 should be displayed in "Country Count" column

- If the city found in Comments_County column then 1 should be displayed in "City Count" column

- if neither country nor City found then 0 should be displayed in "Country Count" and "City Count" column

I have tried this using the set Analysis

=if((count({<Comments_Country={Country}>} Country) > 0),1,0)

But it displays data as follows:

Please suggest

[Anil_Babu_Samineni]

May be add this code with Antonio's code

NoConcatenate

LOAD A, Country, If(SubStringCount(Comments_Country, Country), 1,0) as Comments_Country_Flag, Comments_Country, City, Comments_City,

If(SubStringCount(Comments_City, Country), 1,0) as Comments_City_Flag Resident Table;

Drop Table Table;

And Front end try these expressions

1) Count({<Comments_Country_Flag -= {0}>} Country)

2) Count({<Comments_Country_Flag -= {1}>} Country)