topic Re: 'Dual' not working?please help in QlikView

'Dual' not working?please help

Thu, 09 Jul 2015 13:40:34 GMT

Hi,

I have below script in qvw. when I reload I can able to see in Flag column only START and GONE. Remaining are not loading into Flag column. Please can anyone suggest me is there any wrong in below script?

If (MixMatch(NAME,'XX',

'YY',

'ZZ',

'AA',

'BB',

'CC',

'DD',

'EE',

'FF',

'GG',

'HH',

'II',

'JJ',

'KK',

'LL',

'MM',

'NN',

'OO',

'PP',

'QQ,

'RR',

'SS',

'TT',

'UU',

'VV',

'WW'),Dual('START',1),

If(MixMatch(NAME,'GONE'),Dual('GONE',2),

If(MixMatch(NAME,'QQ,

'RR',

'SS',

'TT',

'UU'),Dual('APP',3)

If(MixMatch(NAME,'XX',

'YY',

'ZZ',

'AA',

'BB',

'CC',

'DD',

'EE',

'FF',

'GG',

'HH',

'II',

'JJ',

'KK',

'LL',

'MM',

'NN',

'OO',

'PP',

'VV',

'WW'),Dual('PROCESS',4),

If(MixMatch(NAME,

'YY',

'ZZ',

'AA',

'BB',

'CC',

'DD',

'EE',

'FF',

'GG',

'HH',

'II',

'JJ',

'KK',

'LL',

'MM',

'NN',

'OO',

'PP',

'QQ),Dual('CON',5)))))) as Flag

Thanks.

Re: 'Dual' not working?please help

swuehl — Thu, 09 Jul 2015 13:44:13 GMT

On first glance, seems like you tested these values already before, so there is no execution path that leads to these Flag values.

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 13:46:24 GMT

yep. is there any thing do I need to add to load all values?

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 13:46:42 GMT

That is because your first and second if statements are covering all the things for anything after that. You cannot assign two flags to one value at the same time in one column.

For example YY cannot be START as well as PROCESS at the same time in one column for one row.

Does that make sense?

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 13:53:19 GMT

May be you can do something like this:

Table

LOAD *,

Dual('START',1) as Flag

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ,'RR','SS','TT','UU','VV','WW');

Concatenate (Table)

LOAD *,

Dual('GONE',2) as Flag

Resident ....

Where MixMatch(NAME,'GONE');

Concatenate (Table)

LOAD *,

Dual('APP',3) as Flag

Resident ....

Where MixMatch(NAME,'QQ,'RR','SS','TT','UU');

Concatenate (Table)

LOAD *,

Dual('PROCESS',4) as Flag

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','VV','WW');

Concatenate (Table)

LOAD *,

Dual('CON',5) as Flag

Resident ....

Where MixMatch(NAME,'YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ);

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 13:54:29 GMT

yes make sense. is there any way we can get two flags? I need all 5 columns as below

START

GONE

APP

PROCESS

CON

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 14:07:25 GMT

Thanks. I used and when I am reloading it is showing field names must be unique within the table

do I need to change anything?

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 14:10:21 GMT

Make sure the older table from where you are picking the data doesn't have a field called Flag. If it does you can rename the new Flag to may be FlagNew

Table

LOAD *,

Dual('START',1) as FlagNew

Resident ....

WhereMixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ,'RR','SS','TT','UU','VV','WW');

Concatenate (Table)

LOAD *,

Dual('GONE',2) as FlagNew

Resident ....

Where MixMatch(NAME,'GONE');

Concatenate (Table)

LOAD *,

Dual('APP',3) as FlagNew

Resident ....

Where MixMatch(NAME,'QQ,'RR','SS','TT','UU');

Concatenate (Table)

LOAD *,

Dual('PROCESS',4) as FlagNew

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','VV','WW');

Concatenate (Table)

LOAD *,

Dual('CON',5) as FlagNew

Resident ....

Where MixMatch(NAME,'YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ);

I hope this will resolve your issue.

Best,

Sunny

Re: 'Dual' not working?please help

swuehl — Thu, 09 Jul 2015 14:11:02 GMT

If your original table is called OriginalTable, load a NewTable like

NewTable:

LOAD *,

Dual('START',1) as Flag

Resident OriginalTable

WhereMixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ,'RR','SS','TT','UU','VV','WW');

Concatenate (NewTable)

LOAD *,

Dual('GONE',2) as Flag

Resident OriginalTable

Where MixMatch(NAME,'GONE');

...

//Continue loading the different flag values

DROP TABLE OriginalTable;

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 14:13:31 GMT

yes. I did the same. but showing error.

Re: 'Dual' not working?please help

swuehl — Thu, 09 Jul 2015 14:20:01 GMT

As Sunindia said, OriginalTable should be the one without the Flag field you want to create, also without your original attempt to create the field.

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 14:23:24 GMT

Or call the new field to be NewFlag instead of Flag

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 14:32:44 GMT

Thanks. I have used this. when I am reloading it is showing still reloading more than 20 minutes. is that because of

concatenate?

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 14:34:57 GMT

Did you drop your Original table or not? If you have not then its creating a synthetic key which could take hours depending on the amount of data. I suggest to stop the script and add Drop Table OriginalTable and re run.

HTH

Best,

Sunny

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 14:41:08 GMT

. Thanks. I did that. now it reload fine. sorry I need one more column as SUCCESS which shows START+GONE

how can I do this? is there any way to do this with Dual? or do I need to use any thing more? Please let me know.

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 14:46:13 GMT

Not sure I understand your requirement. What do you mean when you say START + GONE = SUCCESS? you mean that any of those which fall into these two are also SUCCESS?

If the above statement is true try this:

Table

LOAD *,

Dual('START',1) as FlagNew

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ,'RR','SS','TT','UU','VV','WW');

Concatenate (Table)

LOAD *,

Dual('GONE',2) as FlagNew

Resident ....

Where MixMatch(NAME,'GONE');

Concatenate (Table)

LOAD *,

Dual('APP',3) as FlagNew

Resident ....

Where MixMatch(NAME,'QQ,'RR','SS','TT','UU');

Concatenate (Table)

LOAD *,

Dual('PROCESS',4) as FlagNew

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','VV','WW');

Concatenate (Table)

LOAD *,

Dual('CON',5) as FlagNew

Resident ....

Where MixMatch(NAME,'YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ);

Concatenate (Table)

LOAD *,

Dual('SUCCESS',6) as FlagNew

Resident ....

Where MixMatch(NAME,'XX','YY','ZZ','AA','BB','CC','DD','EE','FF','GG','HH','II','JJ','KK','LL','MM','NN','OO','PP','QQ,'RR','SS','TT','UU','VV','WW', 'GONE');

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 14:54:27 GMT

That's perfect. Thanks. will this work for division?

if I want to show another column as percentage how to do this?

START/GONE

please let me know. sorry for asking more.

EDIT: also please suggest me how to do this. I want to calculate the flag value CON as below

CON+0.5*'XX'

how to do this?

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 14:56:40 GMT

That you should be able to do in the front end, right?

=Count({<NewFlag = {'START'}>} NewFlag)/Count{<NewFlag = {'GONE'}>} NewFlag)

Re: 'Dual' not working?please help

Thu, 09 Jul 2015 15:03:37 GMT

when I am using in front end as expression it is showing 0%

Re: 'Dual' not working?please help

sunny_talwar — Thu, 09 Jul 2015 15:07:23 GMT

Did you name your Flag as Flag or NewFlag? Whatever name you choose, you will need to use that name in this expression: =Count({<NewFlag = {'START'}>} NewFlag)/Count{<NewFlag = {'GONE'}>} NewFlag)