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

[#2]

But, I want to count the the number of rows for each column individually

Thanks

[Gysbert_Wassenaar]

But they are always the same as the number of rows of the table.

[#4]

Yeah... i know what you mean..

But, is it possible to count the rows which have values for each field,,, not the every row

as fieldvaluecount function counting only the rows which have values...(except one row extra)

is it possible to count the number of rows which have values

my table will be like this, when i see it in table box..

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

so, i want to get the count

          fill A Time =3

          fill B Time =4

          fill C Time =3

Thank you

Ganesh

[Gysbert_Wassenaar]

TableTagsWithFillTimeValue:

LOAD

     TagName

     [Fill A Time],

     [Fill B Time],

     [Fill C Time],

     If(Len(Trim([Fill A Time])),1,0) as [Fill A Time Has A Value],

     If(Len(Trim([Fill B Time])),1,0) as [Fill B Time Has A Value],

     If(Len(Trim([Fill C Time])),1,0) as [Fill C Time Has A Value],

FROM

     ...source....

     ;

CountsOfTableTagsWithFillTimeValue:

LOAD

     sum([Fill A Time Has A Value]) as [Number Of Records Where Fill A Time Has A Value],

     sum([Fill B Time Has A Value]) as [Number Of Records Where Fill B Time Has A Value],

     sum([Fill C Time Has A Value]) as [Number Of Records Where Fill C Time Has A Value],

RESIDENT

     TableTagsWithFillTimeValue

     ;

[#6]

Hy Gysbert

with the above code, I am getting the same result as Total number of rows in table

could you please check with that..

[marcus_sommer]

Only with these fields you couldn't check your data - you need to add an unique record-identifier created within your load-script per recno() and/or rowno() and/or a 'Source' to identify the table from which it come from - then a tablebox will display each combination of included fields only once and you won't see any duplicates.

After that I think there will be no differences anymore.

- Marcus

[antoniotiman]

Gysbert,

maybe

if(TagsForPOTime='Fill A',1,0) as  [Fill A Time Has A Value],

instead of Len(Trim(....  that is Always > 0.

[#9]

vTagForPO = FillATime,FillBTime,FillCTime

NoConcatenate

  GetPOTime:

  SELECT TagName as TagsPOTime,

  DateTime as POTime

  FROM

  History

WHERE

  TagName IN$(vTagForPO)

  AND DateTime >= '$(vProdStartTime)'

  AND DateTime <= '$(vThisExecTime)'

  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

  Resident GetPOChangeTime;

Here, I need some function, which can calculate the number of rows for each column for Fill A Time, Fill B Time, Fill C Time

let vRowsFillATime=

let vRowsFillBTime=

let vRowsFillCTime=

Thank you

Ganesh

[#10]

Hi Antonio,

I already, have my code like this

vTagForPO = FillATime,FillBTime,FillCTime

NoConcatenate

  GetPOTime:

  SELECT TagName as TagsPOTime,

  DateTime as POTime

  FROM

  History

WHERE

  TagName IN$(vTagForPO)

  AND DateTime >= '$(vProdStartTime)'

  AND DateTime <= '$(vThisExecTime)'

  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

  Resident GetPOChangeTime;

Here, I need some function, which can calculate the number of rows for each column for Fill A Time, Fill B Time, Fill C Time

let vRowsFillATime=

let vRowsFillBTime=

let vRowsFillCTime=

Thank you

Ganesh

[#11]

sorry Marcus

could you please help me with that.. I have the FiieldNames in a in-memory table which are extracted using an if condition from another table. your suggestion, I dont know, how to add a rowno() for each column.. could you please make those changes in the following code

vTagForPO = FillATime,FillBTime,FillCTime

NoConcatenate

  GetPOTime:

  SELECT TagName as TagsPOTime,

  DateTime as POTime

  FROM

  History

WHERE

  TagName IN$(vTagForPO)

  AND DateTime >= '$(vProdStartTime)'

  AND DateTime <= '$(vThisExecTime)'

  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

  Resident GetPOChangeTime;

Here, I need some function, which can calculate the number of rows for each column for Fill A Time, Fill B Time, Fill C Time

let vRowsFillATime=

let vRowsFillBTime=

let vRowsFillCTime=

Thank you

ganesh