Count if in qlik ( how many value are equals in a column) end order with specific paramiters

[lukeert19]

Hello to all,

I need to create one column in a table with 180k records where I can check how many values are equals in a specific column (SerialL3) without knowing the specific data

normally in excel I'm using  -->  =countif($CW:$CW;CW2) I would like to avoid to use this process because with excel is very slow 🙂

the second issue which I have is to order the same table with specific order

first value found (serial) correspont to the month more recent (second return)

second value found (serial) is the first (month less recent)
both serials must be the same!

same order is for 3 or 4 same serial numbers faund

below an example with final result

		Serial	month	CountReturn
	2°	0000RC3730	12	2
	1°	0000RC3730	10	2
	2°	01007105	1	2
	1°	01007105	10	2
	2°	01007269	1	2
	1°	01007269	10	2
	3°	0100WE7551	1	3
	2°	0100WE7551	12	3
	1°	0100WE7551	12	3
	2°	0100GH8445	11	2
	1°	0100GH8445	10	2

N.B.: the characters of the Serial number could start from 6 to 24 number

who can help me to solve this my trouble?

thank you all in advance

[rubenmarin]

Hi, for the count you can use Count(TOTAL SerialL3), or maybe Count(TOTAL <SerialL3> distinct SerialL3&Month).

I don't understand the requirements about the sort can you post a sample showing how this sample should be sorted?

[lukeert19]

Hello Rubenmarin,

I have tried both forumula only one works well and is (TOTAL <SerialL3> distinct SerialL3&Month()) 🙂

about the second point in attached you can see an example of my file where you can see some details, I think could be clear which what I need 🙂

thanks Rubenmerin for your support

[rubenmarin]

 If it's what I think you can get help of creting the cutted serial as a field, like: Num(Right(SerialL3, 10)) as sortSerial

And sort SerialL3 by expression using this field: Min({1} sortSerial)

For sorting months I think that 1 and 2 comes from an higher year than 10,11 and 12, and that's why them are sorted first, in that case you will need a field that combines year and month to apply as sort like: Year&Num(Month(MonthField), '00') as Period.

Sorting by expression using Min({1} Period) or MinString({1} Period) will sort from most recent to odler. It can cause an issue if there is more than one year data, so month 1 can be of diffrent years, in that case you will need to add year to your table or use Dual() when loading data, but we'll talk about that if it's really necesary.

[lukeert19]

But in this case I haven't one control on serials if are equals or not, or qlik will order also consider if the series have the same values?

I have tried to put the comand  Year&Num(Month([Shipping_date]), '00') as Period inside the LOAD command but appear this issue "Field not found - <year>"

after that I have tried also insert in to the expression field but issue seems as before "name filed not valid: year"

[rubenmarin · Accepted solution]

It should be in script, that was an example, if you don't have a year field you can extract it from Shipping_date:

Year([Shipping_date])&Num(Month([Shipping_date]), '00')

[lukeert19]

Thanks Ruben Marin for your times all works well 😉

[lukeert19]

Hello

Rubenmarin, sorry last question,

if I Would use this formula --> Count(TOTAL <SerialL3> distinct SerialL3&Month(Shipping_date)) in script how I can do it?

beucase during loading all data, the output error is:

Error in expression:

')' expected

---

GDR:

load
SerialL3
,Shipping_date
,Count(TOTAL <SerialL3> distinct SerialL3&Month(Shipping_date)) as GDR
resident invoice
where GDR <> '1';

[rubenmarin · Accepted solution]

Hello, you can't use TOTAL qualifier in script, that one is used to ignore dimensions of the chart where it's used.

And to used aggregation functions like Count() in script mixed with the field to aggregate -like SerialL3 in this case- you need to use a Group by.

As an example it can be something like:

GDR:
load
  SerialL3
  ,Count(distinct Month(Shipping_date)) as GDR
resident invoice
where GDR <> '1'
Group by SerialL3;

The Group by clause should include all fields not inside an aggregation funtion.

If GDR doesn't exists in invoice table and you want to use the one created in this GDR table you will need a precedent load to use the GDR field:

GDR:
load *
where GDR <> '1';
load
  SerialL3
  ,Count(distinct Month(Shipping_date)) as GDR
resident invoice
Group by SerialL3;