Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nsetty
Contributor II

Rows count in Bar chart

I have spreadsheets with data, how can I represent row count in each spreadsheet in bar chart

1 Solution

Accepted Solutions
MVP
MVP

Re: Rows count in Bar chart

Edit the LOAD statement to something like:

LOAD ...

  <load fields from spreadsheets>

  ...

  1 as RowCount,

  RowNo() as rowID,

  FileBaseName() as Source

FROM ;

Then you can use Source as a dimension and Sum(RowCount) as a metric to show the loaded data stats.

(adjust the script to match your exact requirements)

10 Replies
shraddha_g
Honored Contributor III

Re: Rows count in Bar chart

can you elaborate with sample data n sample output?

chooco_co
Valued Contributor

Re: Rows count in Bar chart

Hi Nagesh

First load your excel data and add this script rowno() as row this script automatically generate number of row that you have.

Then in your layout, you can use max(row) or count(row).

You can learn more about functions in Qlik Sense.

https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/Scripting/functions-in-scripts-chart-ex...

https://help.qlik.com/en-us/sense/1.1/pdf/Script%20Syntax%20and%20Chart%20Functions%20Guide.pdf

Regards,

Marco

nsetty
Contributor II

Re: Rows count in Bar chart

Newbie to QlikSense community - Very trivial question


Spreadsheet 1 - Source System 1 -  Row count  - 20k

Spreadsheet 2 - Source System 2 - Row count  - 12k

Spreadsheet 3 - Source System 3 - Row count  - 10k

Spreadsheet 4 - Source System 4 - Row count  - 120k

Spreadsheet 5 - Source System 5 - Row count  - 25k


Data load varies each day.. Have to represent row counts of various spreadsheets in a chart.


Thanks

Nagesh



shraddha_g
Honored Contributor III

Re: Rows count in Bar chart

Create a Dimension with Valuelist() as

Valuelist('Source System 1','Source System 2','Source System 3','Source System 4','Source System 5')

in Measure

USe

Pick(wildmatch(Valuelist('Source System 1','Source System 2','Source System 3','Source System 4','Source System 5'),'Source System 1','Source System 2','Source System 3','Source System 4','Source System 5'),

Count([Field from Source System 1]),

Count([Field from Source System 2]),

Count([Field from Source System 3]),

Count([Field from Source System 4]),

Count([Field from Source System 5])

)

krishna20
Valued Contributor

Re: Rows count in Bar chart

Hi,

Better you concatenate and create flag and use flag in expression you will get seperate count for each spread sheet

spread sheet1

filename() as flag

concatenate

spread sheet 2

filename() as flag

concatenate

filename() as flag

spread sheet 3

MVP
MVP

Re: Rows count in Bar chart

Edit the LOAD statement to something like:

LOAD ...

  <load fields from spreadsheets>

  ...

  1 as RowCount,

  RowNo() as rowID,

  FileBaseName() as Source

FROM ;

Then you can use Source as a dimension and Sum(RowCount) as a metric to show the loaded data stats.

(adjust the script to match your exact requirements)

nsetty
Contributor II

Re: Rows count in Bar chart

Tried with adding measure -

Pick(WildMatch(ValueList('Source System 1'), Count[field in source system 1] )); 

When pulled into Bar chart getting

"An error occurred"

"Internal engine error"

Tried hardcoding the values - Pick(WildMatch(ValueList('Source System 1''), 100 ))

Even then received error.

Thanks

Nagesh

shraddha_g
Honored Contributor III

Re: Rows count in Bar chart

what is the dimension you used before adding the measure?

nsetty
Contributor II

Re: Rows count in Bar chart

Created Dimension using valuelist function

Create a Dimension with Valuelist() as

Valuelist('Source System 1','Source System 2','Source System 3','Source System 4','Source System 5')

Community Browser