Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Edit: I managed to solve my problem using my solution and moving my key for Section Access from the "Sales table" to the "Product table", based on the suggestion by @teiswamsler , thanks everyone for your help.
Hello, I posted a similar question two weeks ago but I ended up realizing that the answer I got wasn't useful for my particular example, so I had to rephrase it.
I have a set of predefined tables: Invoices and Products. In the invoice table I have the data corresponding to a typical sales table: date, SKU, total, client, vendor and branch office, and on the product table I have SKU, Category and Special Product. This last column is important for later and is a string which is "yes" or "no".
Also, I defined section access, that limits users access according to their "branch office".
What I want to do:
What I have done and problems I have found:
Count({<[Special Product]={"yes"},SKU=,[Branch Office]=,[Vendor] =,Client=,Category=>} TOTAL DISTINCT SKU)
Section Access;
LOAD * INLINE [
ACCESS,USERID,BRANCH_OFFICE
USER,COMPANY\sburford,*
USER,COMPANY\user1,X
USER,COMPANY\user2,Y
ADMIN,INTERNAL\SA_SCHEDULER,*
];
Section Application; [...]
Regarding this last aspect, maybe I could define something additional on the Section Access to achieve my desired results? Should I add something to the data model?
Expected result: Note that this should work regardless the filter I apply on data (date, client, vendor, branch office)
SKU | Sales | Margin |
A | 10 | a% |
B | 5 | b% |
C | 0 | - |
D | 0 | - |
Thanks in advance for your help Qlik experts!
Hello Teiswamsler,
If I understand correctly your suggestion, you'd recommend me to alter the Products table and leave a new one, as in the following example?
Branch_Offices:
Load Distinct
[Branch Office] as Unique_Branch_Office
FROM [Sales Table];
For Each Office in FieldValueList('Unique_Branch_Office')
New_Product_Table:
Load Branch_Office & SKU as [Branch Office SKU];
Load
'$(Office)' as Branch_Office,
*
Resident [Product Table];
Drop [Product Table];
Also I guess I'll have to alter the original Sales Table as well:
Sales_Table_New:
Load Branch_Office & SKU as [Branch Office SKU],
*
Resident [Sales Table];
Drop [Sales Table];
Tell me if you have any insights here or any flaw you might see here, I will try this meanwhile.
Can the special products be associated with a special branch that all users have access to in section access?
Hello Jwjackso, I think I haven't understood completely your question. This could be a valuable suggestion. Do you have an example?
Hi SBurford
A short term solution could be to add some dummy data in the fact table.
Add ( concatenate() ) all possible SKU for each of the branch office to the fact table
Hereby all SKU will be possible to show, after Section Access have been applyed.
Br.
Teis
Hello Teiswamsler,
If I understand correctly your suggestion, you'd recommend me to alter the Products table and leave a new one, as in the following example?
Branch_Offices:
Load Distinct
[Branch Office] as Unique_Branch_Office
FROM [Sales Table];
For Each Office in FieldValueList('Unique_Branch_Office')
New_Product_Table:
Load Branch_Office & SKU as [Branch Office SKU];
Load
'$(Office)' as Branch_Office,
*
Resident [Product Table];
Drop [Product Table];
Also I guess I'll have to alter the original Sales Table as well:
Sales_Table_New:
Load Branch_Office & SKU as [Branch Office SKU],
*
Resident [Sales Table];
Drop [Sales Table];
Tell me if you have any insights here or any flaw you might see here, I will try this meanwhile.