topic Re: Mass Comparison? Concatenate? in QlikView

Mass Comparison? Concatenate?

Fri, 02 Aug 2013 20:55:17 GMT

I have a table with users, privileges and privilege permissions.

user1	privilege1	Y
user1	privilege2	Y
user1	privilege3	N
user1	privilege4	Y
user1	privilege5	N
user2	privilege1	Y
user2	privilege2	Y
user2	privilege3	N
user2	privilege4	Y
user2	privilege5	N
user3	privilege1	N
user3	privilege2	Y
user3	privilege3	N
user3	privilege4	Y
user3	privilege5	Y

What I need to do is go through all these and find the users that have duplicate privilege sets, in this example, user1 & user2. Unless there is a better way, I would like to get a table that concatenates the privilege permissions for easy comparison in excel as shown below. Any suggestions?

user1	YYNYN
user1	YYNYN
user1	YYNYN
user1	YYNYN
user1	YYNYN
user2	YYNYN
user2	YYNYN
user2	YYNYN
user2	YYNYN
user2	YYNYN
user3	NYNYY
user3	NYNYY
user3	NYNYY
user3	NYNYY
user3	NYNYY

Re: Mass Comparison? Concatenate?

Tue, 06 Aug 2013 20:25:52 GMT

even something like this would do just fine. any help, please?

	privilege1	privilege2	privilege3	privilege4	privilege5
user1	Y	Y	N	Y	N
user2	Y	Y	N	Y	N
user3	N	Y	N	Y	Y

Re: Mass Comparison? Concatenate?

swuehl — Tue, 06 Aug 2013 20:41:21 GMT

A generic load approach should do the latter.

http://qlikviewnotes.blogspot.co.uk/2010/05/use-cases-for-generic-load.html

Re: Mass Comparison? Concatenate?

Tue, 06 Aug 2013 20:54:38 GMT

Hi,

please check my example, should work as expected.

Regards

Roland

Re: Mass Comparison? Concatenate?

Wed, 07 Aug 2013 18:16:22 GMT

This looks like exactly what I need, Roland. What if instead of two values for the Y/N column, I have 25 values such as 01, 02, 03, 04... 23, 24, 25?

Re: Mass Comparison? Concatenate?

Thu, 08 Aug 2013 10:03:48 GMT

Hi bikeking,

that depends what you want to achieve.

(you said: compare it in excel.) As I am not an excel-user it is not quite clear for me.

My first approach generates a field with all values concatenated like "01020304....".

I think this would be fine if all users have all values.

Otherwise:

An other aproach would be to use the "Generic Load" like swuehl suggested. Sth like:

RawData:

Generic

LOAD @1 AS User,

@2 AS Privilege,

@3 AS Value

FROM

[http://community.qlik.com/message/376723]

(html, codepage is 1252, no labels, table is @1);

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) as Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'RawData.*');

NEXT i

// may be you want to put them together into the "first" table

// so it starts with 2 here:

FOR i = 2 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN (RawData.privilege1) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

Thanks to Rob Wunderlich for his always great postings.

HtH

Roland

Re: Mass Comparison? Concatenate?

Thu, 08 Aug 2013 12:55:07 GMT

I'll have to go the concatenated field route - the excel sheet I imported for the generic load has 3 columns and 35,253 rows. You can imagine the mess of synthetic tables and fields that was created .

I was actually exporting the values from QlikView, so the data's already there in QlikView if that makes a difference. Also the "Y/N" column is an autogenerated column via the load script that has 36 values from "1" to "36":

Table2:

LOAD

       USER,
    PRIVILEGE,
    PERMISSION,
    AutoNumber(PERMISSION) as DUPEFLAG
;

SQL SELECT *
FROM "My.TableA";

Table3:

LOAD

PRIVILEGE,
PRIVILEGEDESCR

;

SQL SELECT

PRIVILEGE,
PRIVILEGEDESC
FROM "My.TableB;

I used DUPEFLAG since the permissions are a mix of letters, numbers and text strings (e.g. No Access). Either way, I just need to get that DUPEFLAG or PERMISSION field concantenated so I can get the equivalent of USER1 - YNYYN etc. My apologies if I didn't give enough info in the beginning. Any further suggestions, though, now that you have an insight to my simple code?

Re: Mass Comparison? Concatenate?

Tue, 13 Aug 2013 14:07:51 GMT

Any ideas?

Re: Mass Comparison? Concatenate?

Thu, 15 Aug 2013 12:08:38 GMT

Turns out all I needed was this:

TABLE4:

LOAD D_THESYSTEMKEY,

CONCAT(PERMISSION) AS CONCATENATEDFIELD

RESIDENT Table2 GROUP BY D_THESYSTEMKEY;