topic Re: Creating Conditional Flags in Script in QlikView

Creating Conditional Flags in Script

Wed, 29 Apr 2015 07:53:05 GMT

Hi All,

I am trying to create a conditional flag/statement in a QV script on a QVD that needs some streamlining

The data is pulled in via ODBC and contains mainly SQL and Excel connections.

I am trying to write a statement using fields from multiple locations to create a flag to make analysis a lot easier.

So far I am at

if(ValueID='*24*' and IncidentType='*Accident*')and ([ListTitle]='*Employee*',0,1,) as 7DayFlag

I know this is probably wrong but can anyone point me in the right direction as to how to make this a flag

Thank you in advance

Re: Creating Conditional Flags in Script

krishna20 — Wed, 29 Apr 2015 08:02:04 GMT

Hi,

if(ValueID='*24*' and IncidentType='*Accident*')and([ListTitle]='*Employee*',0,1,) as 7DayFlag

Is above are from same table?

If yes add this expression in preceding load and proceed.

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 08:04:06 GMT

Thanks for the reply Krishna, but no the fields are loaded in separate tables

Re: Creating Conditional Flags in Script

buzzy996 — Wed, 29 Apr 2015 08:11:50 GMT

use this,

IF(wildmatch(ValueID,'*24*') and wildmatch(IncidentType,'*Accident*') and wildmatch(ListTitle,'*Employee*'),'0','1' ) as Flag

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 08:12:30 GMT

Try like below,

if(wildmatch(ValueID,'*24*') and wildmatch(IncidentType,'*Accident*') and wildmatch([ListTitle],''*Employee*'),0,1) as Flag

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 08:16:41 GMT

Thanks for the answers

It still does not seem to work and I get an error.

Being a complete newbie can this go on a separate line on a new tab or does it need more syntax or to be in the same tab as the load?

Re: Creating Conditional Flags in Script

krishna20 — Wed, 29 Apr 2015 08:22:23 GMT

Hi,

I think you can achieve it by using Apply Map concept.

Re: Creating Conditional Flags in Script

buzzy996 — Wed, 29 Apr 2015 08:23:41 GMT

the above sytanx tested and it's working..

if possible share ur .qvw?

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 08:29:38 GMT

I have attached the QVW it is very much a work in progress.

Datasource has caused a predecessor a lot of headaches and I am trying to go through and streamline it

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:07:32 GMT

Hi,

In Data modeling you need to try like below ,

GeneralData:

//data load;

left join(GeneralData)

mel_InjuryAccidents:

//data load;

left join(GeneralData)

search_Incident_StatusFlags:

//data load;

Noconcatenate

New_GeneralData:

LOAD

if(wildmatch(ValueID,'*24*') and wildmatch(IncidentType,'*Accident*') and wildmatch([ListTitle],'*Employee*'),0,1) as flag

Resident GeneralData;

drop table GeneralData;

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:15:21 GMT

Thanks Harshal for the reply

My noob status however means I am not sure what you mean and where to put in the syntax/script you mentioned.

As before this file is something I have taken over and aside from some excel based reports my script knowledge is low

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:17:44 GMT

You have to perform above in script level only...

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:24:55 GMT

Thanks Harshal, but as mentioned above where in the script does this go?

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:27:07 GMT

Hi,

PFA,

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:37:51 GMT

Thanks Harshall

Unfortunately this does not work and I get an error saying "Field ValueID not Found"

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:39:03 GMT

PFA,

load script & see whether flag is created or not..

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:45:27 GMT

Thanks Harshall

Flag was created but not without ODBC and other drop table errors (about 10)

Re: Creating Conditional Flags in Script

Gabriel — Wed, 29 Apr 2015 09:45:32 GMT

Hi,

Looking at sample file, these fields are coming from different tables. I'll suggest just have extra flag in each tables and flagging wherever the fields are coming from. i.e.

=If(ValueID = '*24*',0,1) as 7DayFlag //This will be in the table that have ValueID

=IF(IncidentType = '*Accident*',0,1) as 7DayFlag //This will be in the table that have IncidentType

=IF([ListTitle] = ''*Employee*'),0,1) as 7DayFlag //This will be in the table that have ListTitle

The other idea is to bring these fields together into 1 table (concatenate), meaning having 3 table for each field and concatenating then loading this single table as Mapping load.

Anyway try the above ideal and let us know what the result is.

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:50:18 GMT

Thanks Gabriel

Would this then create 3 different flags? as I want one flag to mark all 3 criteria so it is easier to use in graphs etc

Toby

Re: Creating Conditional Flags in Script

Wed, 29 Apr 2015 09:53:44 GMT

It also causes issues with Master Calendar script which looks to the General Data table