Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create field 'Type' based on values in other fields

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!

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
AaronCS
BrentBI
HoldenBI +CS

Regards

Andrew

View solution in original post

6 Replies
effinty2112
Master
Master

Hi Aaron,

Try this expression:

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

if(Count(DocumentName)>1,

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

))

NTNAME Type
AaronCS
BrentBI
HoldenBI +CS

Cheers

Andrew

Not applicable
Author

Didn't seem to work for me

asdf.pngload3.png

effinty2112
Master
Master

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
AaronCS
BrentBI
HoldenBI +CS

Regards

Andrew

MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_270159_Pic1.JPG

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

Not applicable
Author

Thanks!

Not applicable
Author

Thanks!