Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Not sure, Why 0 came for Spain Country and Tokyo City?? can you help us to understand the highlighted one. FYI, Here we are
And for Country Count, I believe we are going to exclude comment. Not sure, From Tokyo
Used Count({<Comments -= {'comment'}>} Identifier)
Hi
you want to deal with it in ETL prossess or in frontend ?
Data format needs to be done in script and count can be done in script or displaying the data.
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';
I've done same script to get output as 1st image, But looks like he is getting some zeros that part not done
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';
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
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)