Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nsetty
Partner - Creator II
Partner - Creator 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
jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

10 Replies
shraddha_g
Partner - Master III
Partner - Master III

can you elaborate with sample data n sample output?

marcohadiyanto
Partner - Specialist
Partner - Specialist

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Master III
Partner - Master III

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
Specialist II
Specialist II

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

jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
nsetty
Partner - Creator II
Partner - Creator II
Author

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
Partner - Master III
Partner - Master III

what is the dimension you used before adding the measure?

nsetty
Partner - Creator II
Partner - Creator II
Author

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