topic Re: Display data along with keyword count in QlikView

Display data along with keyword count

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Display data along with keyword count

Anil_Babu_Samineni — Fri, 27 Oct 2017 03:14:31 GMT

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

Re: Display data along with keyword count

Anil_Babu_Samineni — Fri, 27 Oct 2017 03:20:19 GMT

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

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

Re: Display data along with keyword count

woshua5550 — Fri, 27 Oct 2017 03:24:39 GMT

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

Re: Display data along with keyword count

Anonymous — Sat, 28 Oct 2017 03:38:56 GMT

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

Re: Display data along with keyword count

antoniotiman — Sat, 28 Oct 2017 07:19:40 GMT

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

Re: Display data along with keyword count

Anil_Babu_Samineni — Sat, 28 Oct 2017 07:29:38 GMT

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

Re: Display data along with keyword count

antoniotiman — Sat, 28 Oct 2017 08:07:27 GMT

If You have sequence like this

Country

City

Country

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

Re: Display data along with keyword count

Anonymous — Sat, 28 Oct 2017 11:49:57 GMT

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

Re: Display data along with keyword count

Anil_Babu_Samineni — Sat, 28 Oct 2017 12:22:59 GMT

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)

Re: Display data along with keyword count

antoniotiman — Sat, 28 Oct 2017 13:07:08 GMT

Expressions :

Sum(If(Comments_Country Like '*'&Country&'*',1))

Sum(If(Comments_City Like '*'&City&'*',1))