counting unique codes

[tolgaclk]

Hi

How do I formulate the order I specified in the table below?

COD	ID	ORDER
234234	ENGİN	1
23423	ENGİN	2
4235	MESUT	1
123412	CENGİZ	1
1423	SEDAT	1
436	SEDAT	2
67	SEDAT	3
56856	NURETTİN	1
78	HASAN	1
4563423	SEBAHATTİN	1
235	IŞIK GIDA	1
6546	ABDULKADİR	1
578	ABDULKADİR	2
578567434	ABDULKADİR	3
34564	ABDULKADİR	4
56879685	ABDULKADİR	5
3546352	ENGİN	3
567476	ENGİN	4
967857	ENGİN	5
568567	NURETTİN	2

[Clement15]

Hello,

Can you clarify your basic data and what you want to do?

[marcus_sommer]

It could be done with interrecord-functions within a sorted resident load, for example like:

load *, if(ID = previous(ID), peek('ORDER') + 1, 1) as ORDER
resident X order by ID;

[marksouzacosta]

Hi @tolgaclk,

I did something similar to @marcus_sommer. Following the code:

TempMyTable:
LOAD
  COD,
  ID,
  RecNo() AS RowLine
INLINE [
COD,ID
234234,ENGİN
23423,ENGİN
4235,MESUT
123412,CENGİZ
1423,SEDAT
436,SEDAT
67,SEDAT
56856,NURETTİN
78,HASAN
4563423,SEBAHATTİN
235,IŞIK GIDA
6546,ABDULKADİR
578,ABDULKADİR
578567434,ABDULKADİR
34564,ABDULKADİR
56879685,ABDULKADİR
3546352,ENGİN
567476,ENGİN
967857,ENGİN
568567,NURETTİN
];


MyTable:
LOAD
    COD,
    ID,
    If(Match(ID, Peek(ID)),Peek(ORDER)+1,1) AS ORDER
RESIDENT
	TempMyTable
ORDER BY
    ID,
	RowLine
;

DROP TABLE TempMyTable;

Regards,

Mark Costa

[tolgaclk]

Thank you for the answers. but it didn't work. Imagine that these sales are 100 thousand units. and each has a separate code. I want to perform a counting operation as in the table I showed in a new column that I will open in Qlik.

[marcus_sommer]

The above hinted approach to use interrecord-functions will work for the described scenario even if there are millions of records and also if the real case is a bit more complex and might need some if-loops more and/or with more result-fields and/or running it twice (forwards + backwards) or ...

Of course such approach will need more run-time as a pure table load without any transformation but usually it happens within an acceptable time - and everything might be further optimized by embedding it within incremental logic.

Therefore I suggest you give it a try.

[tolgaclk]

The formula below does not work. I need a formula to sort like this.

Aggr(If(Previous([Mobis.Müşteri Kodu])<>[Mobis.Müşteri Kodu], 1, RowNo(TOTAL <[Mobis.Müşteri Kodu]>)), [Mobis.Müşteri Kodu])

Mobis.Müşteri Kodu	Sıra

36518

1

36518

2

36553

1

44002

1

44002

2

44008

1

44008

2

44008

3

[marcus_sommer]

A table in Qlik is a chart which dimensionality is created by the combination of the used fields (in the dimensions as well as within the expressions). Therefore there is no overhead of n rows/columns resulting in n cells like in a table-calculation tools. This leads further to the fact that no dimension-value has a fixed position else it depends on various aspects like the ordering of the dimensions/expression-results, the selection-state, object-properties ...

All of it made it not mandatory impossible to display any kind of position-information but it may depending on the scenario quite difficult and is probably requiring (nested) aggr() to show such calculated dimensions. Means a high complexity and more or less serious disadvantages in regard to the usability and performance.

Simpler and probably more expedient would be to use normal expressions in appropriate designed objects (only serving this purpose and not fulfilling n requirements) with a (combination) of rowno() and/or rowno(total) or a rank() with/without a total-statement against an (extra) expression.

If there is no dependency of the positioning against any user-interactions you shouldn't try it within the UI else using the hinted script-solutions.