Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Display data along with keyword count

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

   

IdentifierDataComments
CountryJapancomments Japan
CityTokyocomments
CountrySpaincomment
CityMadridCity Madrid
CountryFranceFrance country Paris City
CityParisCity 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.

      

CountryCityComments_CountryComments CityCountry CountCity count
JapanTokyocomments Japancomments Tokyo comments10
SpainMadridcommentCity Madrid01
FranceParisFrance country Paris CityCity Paris11
Total22

Please Suggest

10 Replies

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator III
Creator III

Hi

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

Contributor
Contributor

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

Master III
Master III

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Master III
Master III

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';

Contributor
Contributor

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

     

CountryCityComments_CountryCounty CountCity Count
JapanTokyocomments Japan10
SpainMadridcomment00
FranceParisFrance country Paris City11

- 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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)