Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER 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