use of FieldValueCount(getting 1 row more than the number of rows)

[#1]

Hi all,

I Used  FieldValueCount function to count the number of rows of a field  in a column, in detail to count the number of rows in each column of my table.

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/fieldv...

My problem is, i am getting one row more than the rows of that i have in each column

can anyone help me with this/.. How a FieldValueCount function works

Thanks

Ganesh

[antoniotiman · Accepted solution]

NoConcatenate

  FactGetPOChangeTime:

  LOAD

  TagsForPOTime,

  if(TagsForPOTime='Fill A',POTime,0) as FillATime,

  if(TagsForPOTime='Fill A',1,0) as CountA,

  if(TagsForPOTime='Fill B',POTime,0) as FillBTime,

  if(TagsForPOTime='Fill C',POTime,0) as FillCTime

  Resident GetPOChangeTime;

TotalCount:

Load

Sum(CountA) as TotalCountA

Resident FactGetPOChangeTime:

Let vRowsFilATIme = Peek('TotalCountA');

Drop Table TotalCount;

[marcus_sommer]

My suggested extension isn't for a specific column else they are meant for the whole table:

NoConcatenate

  FactGetPOChangeTime:

  LOAD

  TagsForPOTime,

  if(TagsForPOTime='Fill A',POTime,0) as FillATime,

  if(TagsForPOTime='Fill B',POTime,0) as FillBTime,

  if(TagsForPOTime='Fill C',POTime,0) as FillCTime,

  rowno() as RowNo,

  recno() as RecNo, // within a resident-load without where-clauses recno will be the same like rowno

  'FactGetPOChangeTime' as Source

  Resident GetPOChangeTime;

Try this and add these new fields to your tablebox and look if the FillTime-values in your table looked different to:

TagName	Fill A Time	Fill B Time	Fill C Time
Tag A	06:00
Tag A	06:05
Tag A	06:09
Tag B		06:02
Tag B		06:03
Tag B		06:05
Tag B		06:06
Tag C			06:03
Tag C			06:05
Tag C			06:07

- Marcus

[#4]

Thank you Antonio.. i got the result

[#5]

Hi Marcus,

I tried it but, i am getting the row no and rec no columns both same

please find the screenshot

[#6]

Thank you mayil for helping me

[#7]

Thank you gysbert for helping me