Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maya_yurika
Contributor
Contributor

How to create new field with group by in qlikview

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:

IDID NameID Year MonthProduct IDOffer TypeMonthNo of productscheck offers
22A company2019-01applesingle2133
22A company2019-01applesingle311
22A company2019-01applesingle4151
22A company2019-01applesingle552
44B company2019-08applesingle2103
44B company2019-08watermelonsingle251
44B company2019-08watermelonsingle421
55C company2019-08applesingle2103
55C company2019-01applesingle552

 

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

3 Replies
yassinemhadhbi
Creator II
Creator II

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 

Best Regards
Yassine Mhadhbi
maya_yurika
Contributor
Contributor
Author

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)

commQV34.PNG