How to create new field with group by in qlikview

[maya_yurika]

Hi,

I am new in QlikView and I have the following situation. I have a table from a load with multiple fields and I want to count how many IDs are using the same Product ID in each month. So I need to create a new column named: "check offers" as per below table:

ID	ID Name	ID Year Month	Product ID	Offer Type	Month	No of products	check offers
22	A company	2019-01	apple	single	2	13	3
22	A company	2019-01	apple	single	3	1	1
22	A company	2019-01	apple	single	4	15	1
22	A company	2019-01	apple	single	5	5	2
44	B company	2019-08	apple	single	2	10	3
44	B company	2019-08	watermelon	single	2	5	1
44	B company	2019-08	watermelon	single	4	2	1
55	C company	2019-08	apple	single	2	10	3
55	C company	2019-01	apple	single	5	5	2

So far my script looks like this:

Table1:
LOAD [D],
[ID Name],
[ID Year Month],
[Product ID],
[Offer Type],
[Month],
[No of products]

FROM
[\\xxxxxl\xxxxx\xxxxx\xxxxx\products.qvd]
(qvd);

Table2:

LOAD
[Product ID] as [Product],
[Month] as [Month_date],
count([Products ID]) as [check offer]

RESIDENT TB1
GROUP BY
[Month],
[Product ID];

But this doesn't bring me the result I want.

Can you help with a solution? I want to keep all columns from my load + create the new column by 2 condition (to count how many IDs are using the same Product ID in each month).

Thank you

[yassinemhadhbi]

i think you need first to create new column using the function Autonumber for the two columns Id and Month and product so it will be Autonumber(Id&&productId&Month) : 

This Function will help you create a unique number for each time there's a row in ur data that has the same ID , PRODUCTID and Month

Than you can do a simple count 

[maya_yurika]

Thank you. Is working by this method. But now I am facing another issue. How can I count after joining 2 tables?

for example in this situation:

Table1:

LOAD [D],
[ID Name],
[ID Year Month],
[Product ID],
[Offer Type],
[Month],
[No of products]

FROM
[\\xxxxxl\xxxxx\xxxxx\xxxxx\products.qvd]
(qvd);

Table2:

LOAD
*,
AutoNumber([Product ID]&[Month]) as [Offer ID]
RESIDENT Table1;
DROP TABLE Table1;

In order to count the column [Offer ID] I created Table 3 as below but I have an error after reload:

Table 3:
LOAD*,
count([Offer_ID]) as [check offer]
RESIDENT Table2
group by
[ID],
[ID Name],
[ID Year Month],
[Product ID],
[Offer Type],
[Month],
[No of products],
[Offer ID]

drop table Table2;

Am I doing wrong the count?

Thks

[Saravanan_Desingh]

Try this,

Dimension: ID, Product ID

Expression: Count(DISTINCT TOTAL <[Product ID],Month>ID)