topic Re: Create field 'Type' based on values in other fields in QlikView

Create field 'Type' based on values in other fields

Fri, 04 Aug 2017 15:05:38 GMT

Any help is appreciated.

I have Users and Documents.

Each User can be tied to one or more documents, which that user is allowed to view.

I have 5 documents named: AR, SA, IA, ED, CS

I have 3 users: Holden, Aaron, Brent

NTNAME, DocumentName

Holden,AR

Holden,SA

Holden,IA

Holden,ED

Holden,CS

Aaron,CS

Brent,AR

Brent,SA

Brent,IA

Brent,ED

The above data linking Users and Documents is in an existing QVD

There are three different ‘Types’ of users based on the groups of documents they are assigned.

Aaron would be a type of CS

Holden would be a type of BI + CS

Brent would be a type of BI

BI has access to at least one document but its not CS

CS has access to only CS

BI+CS has access to at least one non-CS doc as well as the CS doc.

How can I assign this ‘Type’ in script?

Create another field called type based on the above parameters?

Thanks!

Re: Create field 'Type' based on values in other fields

effinty2112 — Fri, 04 Aug 2017 17:20:57 GMT

Hi Aaron,

Try this expression:

if(Only(DocumentName)='CS','CS',

if(Count(DocumentName)>1,

if(WildMatch(concat(DocumentName,'|'),'*CS*'),'BI +CS','BI')

))

NTNAME	Type
Aaron	CS
Brent	BI
Holden	BI +CS

Cheers

Andrew

Re: Create field 'Type' based on values in other fields

Fri, 04 Aug 2017 18:13:28 GMT

Didn't seem to work for me

Re: Create field 'Type' based on values in other fields

effinty2112 — Fri, 04 Aug 2017 19:40:26 GMT

Hi Aaron,

This is all the script you need for the expression to work in a straight table

Data:

LOAD * INLINE [

NTNAME, DocumentName

Holden,AR

Holden,SA

Holden,IA

Holden,ED

Holden,CS

Aaron,CS

Brent,AR

Brent,SA

Brent,IA

Brent,ED

];

If you want the field to be calculated in script then try:

Data:

LOAD

NTNAME,

Concat(DocumentName,'|') as ConcDoc

Group by NTNAME;

LOAD * INLINE [

NTNAME, DocumentName

Holden,AR

Holden,SA

Holden,IA

Holden,ED

Holden,CS

Aaron,CS

Brent,AR

Brent,SA

Brent,IA

Brent,ED

];

[Typed Data]:

LOAD

if(ConcDoc='CS','CS',

if(SubStringCount(ConcDoc,'|')>0,

if(WildMatch(ConcDoc,'*CS*'),'BI +CS','BI')

)) as Type;

load * Resident Data;

DROP Table Data;

Giving:

NTNAME	Type
Aaron	CS
Brent	BI
Holden	BI +CS

Regards

Andrew

Re: Create field 'Type' based on values in other fields

MarcoWedel — Fri, 04 Aug 2017 23:08:20 GMT

Hi,

another solution might be:

tabNameDoc:
LOAD * INLINE [
    NTNAME, DocumentName
    Holden, AR
    Holden, SA
    Holden, IA
    Holden, ED
    Holden, CS
    Aaron, CS
    Brent, AR
    Brent, SA
    Brent, IA
    Brent, ED
];

Join
LOAD NTNAME,
    Concat(DISTINCT If(DocumentName='CS','CS','BI'),'+') as Type
Resident tabNameDoc
Group By NTNAME;

hope this helps

regards

Marco

Re: Create field 'Type' based on values in other fields

Mon, 07 Aug 2017 14:56:10 GMT

Thanks!

Re: Create field 'Type' based on values in other fields

Mon, 07 Aug 2017 14:56:41 GMT

Thanks!