topic Re: How to write expression to add column into table in QlikView

How to write expression to add column into table

Tue, 05 May 2015 13:35:16 GMT

Hi,

I have expression as below.

when I am using this it is showing error as Error in expression:If takes 2-3 parameters

If(MixMatch(Zinc,'Present',

IF (MixMatch( Coal,'Marbel Transfer'),'Ready',

If(MixMatch(Zinc,'XX','YY','CC','SS','VV'),'Ready',

If(MixMatch(Coal,'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ',

If (Not Match(Coal,'Marbel Transfer'),'Ready')))))))) as [Ready],

Please can anyone suggest me how to set this?

Thanks.

Re: How to write expression to add column into table

sunny_talwar — Tue, 05 May 2015 13:42:01 GMT

Your if statement doesn't make sense. If Coal matchs 'Marbe Transfer' [Ready] should have value 'Ready' and if it doesn't match 'Marbe Transfer' again it is called 'Ready'??

If(MixMatch(Zinc,'Present',

IF (MixMatch( Coal,'Marbel Transfer'),'Ready',

If(MixMatch(Zinc,'XX','YY','CC','SS','VV'),'Ready',

If(MixMatch(Coal,'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ',

If (Not Match(Coal,'Marbel Transfer'),'Ready')))))))) as [Ready],

If your provide the logic, we might be able to provide you with a better if statement.

With regards to the error:

You are missing ,'Ready' for your first If statement

=If(MixMatch(Zinc,'Present'), 'Ready',

IF(MixMatch(Coal,'Marbel Transfer'),'Ready',

If(MixMatch(Zinc,'XX','YY','CC','SS','VV'),'Ready',

If(MixMatch(Coal,'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ',

If(Not Match(Coal,'Marbel Transfer'),'Ready')))))) as [Ready]

I hope this helps.

Best,

Sunny

Re: How to write expression to add column into table

Wed, 06 May 2015 10:54:14 GMT

sorry I should have use NotReady in above line where there is Not match.

Please see below this is my requirement:

my excel sheet have below columns

ExcelA:

Date Id Name Zinc Coal Spent

I have to insert one column 'Ready' in between 'Zinc' and 'Coal' in ExcelA sheet

for that I have to use condition as below

when Zinc = 'Present' and Coal = 'Marbel Transfer' then [Ready] = Ready
when Zinc = 'XX','YY','CC','SS','VV' then [Ready] = Ready
When Zinc = 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ' and Coal not equal to 'Marbel Transfer' then [Ready] = NotReady

how can I write script by using above conditions. Please help.

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 11:00:55 GMT

Still not sure about this:

When Coal = 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ' and Coal not equal to 'Marbel Transfer'

Because if Coal is NOTXX, then it def. won't be Marbel Transfer (so there is no point checking for Coal not equal to 'Marbel Transfer' here. Try the below if the conditions you have given me is what you are looking for:

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready',

If(MixMatch(Coal, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ'), 'NotReady')) as Ready

I hope this will help.

Best,

Sunny

Re: How to write expression to add column into table

Wed, 06 May 2015 11:05:23 GMT

sorry there is typo mistake. I edited it. Please see below

when Zinc = 'Present' and Coal = 'Marbel Transfer' then [Ready] = Ready

when Zinc = 'XX','YY','CC','SS','VV' then [Ready] = Ready

When Zinc = 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ' and Coal not equal to 'Marbel Transfer' then [Ready] = NotReady

Re: How to write expression to add column into table

PrashantSangle — Wed, 06 May 2015 11:05:25 GMT

Hi,

use if with wildmatch()

try like

if(WildMatch(Zinc,'Present') and wildmatch(Coal,'Marbel Transfer'),'Ready',

if(wildmatch(Zinc,'XX','YY','CC','SS','VV'),'Ready',

if(wildmatch(Coal,'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ','Marbel Transfer'),'Not Ready')

)

) as Ready_NotReady_Flag

Regards

Re: How to write expression to add column into table

PrashantSangle — Wed, 06 May 2015 11:06:25 GMT

Hi,

use if with wildmatch()

try like

if(WildMatch(Zinc,'Present') and wildmatch(Coal,'Marbel Transfer'),'Ready',

if(wildmatch(Zinc,'XX','YY','CC','SS','VV'),'Ready',

if(wildmatch(Zinc,'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ') and wildmatch(Coal,'Marbel Transfer'),'Not Ready')

)

) as Ready_NotReady_Flag

Regards

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 11:07:20 GMT

Try this:

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready',

If(MixMatch(Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ'), 'NotReady')) as Ready

Re: How to write expression to add column into table

maxgro — Wed, 06 May 2015 11:08:39 GMT

if(

(Zinc='Present' and Coal='Marbel Transfer')

or (match(Zinc, 'XX','YY','CC','SS','VV'))

or (match(Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ') and Coal<>'Marbel Transfer'),

'Ready',

'Not ready'

) as Ready

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 11:12:52 GMT

Forgot to update the last formula (Use this one and disregard the last one)

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready',

If(MixMatch(Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ') and Coal <> 'Marbel Transfer', 'NotReady')) as Ready

Re: How to write expression to add column into table

Wed, 06 May 2015 11:16:58 GMT

Thanks. I have used this by making change by considering your previous one. when I used this the column 'Zinc' is showing only value 'Present' and the column 'Zinc' is not showing values 'XX', 'YY', 'CC', 'SS', 'VV'

Please let me know do I need to change anything here?

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 11:19:19 GMT

I didn't understand? Do you want to change the conditions???

Re: How to write expression to add column into table

Wed, 06 May 2015 11:27:02 GMT

Please see attached. Zinc list box is showing only value 'Present' but I need Zinc list box should show

Present, ,'XX','YY','CC','SS','VV'

EDIT: I don't want to change the conditions. when I used table box. all columns are fine but Zinc column is showing values only 'Present'.

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 12:21:14 GMT

But we are not making any changes to Zinc. This is your database/data issue. I am helping you create a new column Ready which only uses what you have in Zinc. Does that make sense?

Sunny

Re: How to write expression to add column into table

Wed, 06 May 2015 12:55:59 GMT

yes it makes sense. data is populating into Ready except wherever in source data Zinc= NOTZZ and Coal <> 'Marbel Transfer'

Is it again data problem?

why I am asking is data is populating fine wherever there is

Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC') and Coal <> 'Marbel Transfer'

please let me know

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 13:00:33 GMT

Oh so you mean that it doesn't show NotReady when Zinc is NOTZZ and Coal <> 'Marbel Transfer'?

That is strange, would you be able to share a sample application to check this?

Re: How to write expression to add column into table

Wed, 06 May 2015 13:58:13 GMT

sorry I should have add one more to the condition. so, I need to show

NotReady when Zinc is Present and Coal <> 'Marbel Transfer'?

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready',

If(MixMatch(Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ','Present') and Coal <> 'Marbel Transfer', 'NotReady')) as Ready

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 14:04:26 GMT

Try this:

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready',

If(MixMatch(Zinc, 'NOTXX','NOTYY','NOTSS','NOTCC','NOTZZ', 'Present') and Coal <> 'Marbel Transfer', 'NotReady')) as Ready

Re: How to write expression to add column into table

Wed, 06 May 2015 14:23:23 GMT

Thanks. Tried this. still not populating data into Ready. do we need to use any TRIM functions. I checked excel file as well everything is fine.

Re: How to write expression to add column into table

sunny_talwar — Wed, 06 May 2015 14:26:24 GMT

One issue I see in the qvw you have attached is that the Coal column is almost empty

Is the goal is to fill NotReady everywhere we don't have Ready in column Ready? If that is the goal you can probably use this:

If((Zinc = 'Present' and Coal = 'Marbel Transfer') or (MixMatch(Zinc, 'XX', 'YY', 'CC', 'SS', 'VV')), 'Ready', 'NotReady')) as Ready