![Contributor](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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
![Creator II](/html/@6B5206F7B2906F7249673391408B4801/rank_icons/Community_Gamification-Ranking-Icons_16x16-Creator.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Yassine Mhadhbi
![Contributor](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this,
Dimension: ID, Product ID
Expression: Count(DISTINCT TOTAL <[Product ID],Month>ID)
![](/skins/images/DA93A62CB05DE8EBAC29EA917B0E5CFF/responsive_peak/images/icon_anonymous_message.png)