Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table which consists of the following data, for example:
Item No | Attribute A | Attribute B | Counted duplicates |
---|---|---|---|
1234 | w | a | 0,25 |
1234 | x | b | 0,25 |
1234 | y | c | 0,25 |
1234 | z | d | 0,25 |
2345 | f | e | 1 |
3456 | g | t | 0,33 |
3456 | h | n | 0,33 |
3456 | i | m | 0,33 |
...
I need to create that last column. To do that I need something like:
1 / (how many times does an Item No exist in the table)
My approaches until now are:
1/count([Item No])
1/count(if([Item No]=[Item No], [Item No]))
1/(count(distinct [Item No])/count([Item No]))
(count(distinct [Item No])/count([Item No]))
But none of them produces the results I need.
Any help would be greatly appreciated.
For script based solution, try this out:
Table:
LOAD [Item No],
[Attribute A],
[Attribute B]
FROM
[https://community.qlik.com/thread/220880]
(html, codepage is 1252, embedded labels, table is @1);
Left Join(Table)
LOAD [Item No],
Count([Item No]) as Total
Resident Table
Group By [Item No];
FinalTable:
LOAD *,
1/Total as NewColumn
Resident Table;
DROP Table Table;
Hi,
Is the attached qvw what you need ?
Regards,
Filip
Try this:
1/Count(TOTAL [Item No])
1/Count(TOTAL<[Item No]> [Item No])
That would help. How did you create the last column in the loading script?
Hi,
that does the same thing as 1/count([Item No]).
Isn't this what you wanted?
Hi Sunny T,
Sorry but it seems I haven't made myself fully clear.
Your solution works just fine in a created Table as an element in Qlik Sense.
But I need this column in my original data, therefore I need a solution for the loading script.
If I try your approach in my loading script like this
,1/Count(TOTAL<[Item No]> [Item No]) as [Item_Counter];
I get the following error message:
Error in expression: ')' expected
For script based solution, try this out:
Table:
LOAD [Item No],
[Attribute A],
[Attribute B]
FROM
[https://community.qlik.com/thread/220880]
(html, codepage is 1252, embedded labels, table is @1);
Left Join(Table)
LOAD [Item No],
Count([Item No]) as Total
Resident Table
Group By [Item No];
FinalTable:
LOAD *,
1/Total as NewColumn
Resident Table;
DROP Table Table;
This works just fine. Many thanks!