Mass Comparison? Concatenate?

[#1]

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

[#2]

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

[swuehl]

A generic load approach should do the latter.

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

[#4]

Hi,

please check my example, should work as expected.

Regards

Roland

[#5]

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?

[#6]

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

[#7]

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?

[#8]

Any ideas?

[#9 · Accepted solution]

Turns out all I needed was this:

TABLE4:

LOAD D_THESYSTEMKEY,

    CONCAT(PERMISSION) AS CONCATENATEDFIELD

RESIDENT Table2 GROUP BY D_THESYSTEMKEY;