topic Re: composite key in QlikView

composite key

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have below 2 tables. I want to link both tables. Product, Loation, Domain fields are common between both tables. So when user click on particular product/Location/Domain the data in other table should be filtered when values match.

But the 'ALL' value in Status table has to link all value in Sales table in that field.

eg- When a user click on Product- BB, then below rows should be filtered in status table.

output--

Product, Location, Domain, Status

BB, XX, 200, description list

BB, ALL, 500, content management

BB, ALL, ALL, definition list

ALL, ALL, ALL, context for the text

Suppose if user select Product- BB and Location-200, then only 1st,3rd,4th rows should be filtered from above result.

Sales:

LOAD * INLINE [

Product, Location, Domain, Sales

AA, XX, 100, 3262

BB, XX, 200, 267437

BB, XX, 500, 353

CC, YY, 500, 226

DD, ZZ, 500, 37

EE, KK, 100, 23532

];

Status:

LOAD * INLINE [

Product, Location, Domain, Status

AA, XX, 100, list item

BB, XX, 200, description list

CC, YY, 500, term in a description list

DD, ZZ, 500, incorporate a definition

EE, KK, 100, visually

CC, ALL, ALL, DL element

BB, ALL, 500, content management

BB, ALL, ALL, definition list

ALL, ALL, ALL, context for the text

];

Please help!

Re: composite key

vvira1316 — Mon, 02 Oct 2017 16:45:46 GMT

Hi Suraj,

Is value 'ALL ' part of data in each dimension or you have included it for purpose of illustration?

Would user be selecting values in each dimensions? Would you like to have value 'ALL' appear in List Box for that particular dimension or how app should know that user is looking for information pertaining to ALL option?

I think understanding of that will help in finding a solution / arriving at solution.

BR,

Vijay

Re: composite key

surajap123 — Mon, 02 Oct 2017 16:49:57 GMT

Hi Vijay,

Thanks for your reply.

ALL is the real value in the data in the Status table. This value is not shown in listbox, it only represents to associate all values(as per the example i have show initially.)

User doesn't require to know that he is looking at data specific to ALL. The main table is Sales. So when user selects values in sales table, the Status table should be filtered.

Re: composite key

vvira1316 — Mon, 02 Oct 2017 17:11:44 GMT

Hi Suraj,

Your requirement are still not clear but first of all to get rid of SynKey I would create a composite key as follows.

SalesTmp:
LOAD * INLINE [
Product, Location, Domain, Sales
AA, XX, 100, 3262
BB, XX, 200, 267437
BB, XX, 500, 353
CC, YY, 500, 226
DD, ZZ, 500, 37
EE, KK, 100, 23532
];

StatusTmp:
LOAD * INLINE [
Product, Location, Domain, Status
AA, XX, 100, list item
BB, XX, 200, description list
CC, YY, 500, term in a description list
DD, ZZ, 500, incorporate a definition
EE, KK, 100, visually
CC, ALL, ALL, DL element
BB, ALL, 500, content management
BB, ALL, ALL, definition list
ALL, ALL, ALL, context for the text
];

NoConcatenate
Sales:
LOAD
Product as SalesProduct
,Location as SalesLocation
,Domain as SalesDomain
,Sales
,[Product] & [Location] & [Domain] as Tablekey
Resident SalesTmp;

NoConcatenate
Status:
LOAD
Product as StatusProduct
,Location as StatusLocation
,Domain as StatusDomain
,Status
,[Product] & [Location] & [Domain] as Tablekey
Resident StatusTmp;

DROP Table SalesTmp;
DROP Table StatusTmp;

Can you please advise now how the selection should happen and what values will have to be seen by user