topic How to apply condition for qvd file with multiple values for one field? in QlikView

How to apply condition for qvd file with multiple values for one field?

jagannalla — Mon, 18 Jul 2011 13:00:22 GMT

Hi,

I've a qvd file with filds 4 with data as shown below:

- I had a list box with B field as shown below:

- Based on selection i want to load qvd file. If user selects only one value we can store that value in one variable and we can use that variable in where condition of qvd file. If user selects multiple values also we can store in a single variable but, how can apply this variable in where condition of qvd file.

For Eg:

LOAD A,

FROM

Table1.qvd

(qvd)

WHERE(B = 1);

- i need to apply like this for all fields.

- For your reference i'm attaching my qvw file also.

How to apply condition for qvd file with multiple values for one field?

Mon, 18 Jul 2011 14:32:15 GMT

Hi,

I am not sure, why you want to trigger a refresh a file based on your selection.

Your approach should be to Load all the Data Source and Link them by adding a flag.

Tabl1:

Load B FROM Table1.qvd (qvd);

Tab2:

Load *,

1 AS B FROM Table2.qvd (qvd);

Tab3:

Load *,

34 AS B FROM Table3.qvd (qvd)

and so on.

Based on the selection you should be able to Hide/Display the Data.

Regards,

Amit

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 04:54:35 GMT

Hi,

Actually the think is i had two qvw files. The user can make selections in one qvw file. I'm storing all this selections in different variables and i'm passing this variable to another qvw file.

For Eg:

In first qvw file i had qvd file like this.

Load A,B,C,P,Q,R,S from Table1.qvd;

- I' had taken in listbox like this:

- In button actions i written for variable(vSend) with value GetFieldSelections(F1,',',50) by using this button i'm navigating to another qvw file with this variable.

In second qvw file i had written like this

Load A,B,C,$(vSend) from Table1.qvd;

- Now it loads all fields of particular qvd file. But, while loading the qvd file it should load based on selections done for B,C etc..as i said previously.

How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 06:40:27 GMT

What i understood is u want to achieve a IN condition by passing multiple values so use the match function for that

Load * from table.qvd

where match(b,$(var);

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 08:39:31 GMT

Thanks a lot qliksus..

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 10:17:04 GMT

Hi,

I need some help from you again..

- I'm storing all the selections of the following listbox in one variable like this:

- Now the variable vCdata holds the value vCdata=GoodReads,MissingReads,NoReads.

- But how to keep where condition based on this variable to qvd file.

- The field C contains the data like this.

Main1:

LOAD A,

if(C='No Read','NoReads',if(Len(C)>0,'GoodReads','MissingReads'))as C1,

FROM

Table1

where match(B,$(var) and ..........;

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 10:53:36 GMT

Sorrry for the late reply can u explain again i didnt understand wat u want

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 11:29:06 GMT

ok i'll explain you with detail.

- My qvd file contains the data like this:

- In first qvw file i written in the editscript like this:

Table1:

LOAD

if(A='No Read','NoReads',if(Len(A)>0,'GoodReads','MissingReads'))as A1,

FROM

Table1.qvd;

DisplayData:

LOAD * INLINE [

NoReads

GoodReads

MissingReads

];

- Now i had take F1 in listbox and i'm passing all selections of this listbox in variable to another qvw file(i.,e vSend=GoodReads,MissingReads,NoReads)

- In second qvw file i'm loading the qvd file as you said previously. It is working for the field B. B'coz B fields holds the data of passed variable so condition is working properly with Match condtion.

- But now A fields doesn't hods the data of variable vSend.

- I want to apply conditon for A field in second qvw like this

Table1:

LOAD

if(A='No Read','NoReads',if(Len(A)>0,'GoodReads','MissingReads'))as A1,

FROM

Table1.qvd where A1='No Read' or A1= 'GoodReads' or A1= MissingReads;

- But i dont want to give like above i want to give like Match(A1,$(vSend))

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 11:40:37 GMT

Try like this then

Table1:

LOAD

A1,

where A1='No Read' or A1= 'GoodReads' or A1= MissingReads;

LOAD

if(A='No Read','NoReads',if(Len(A)>0,'GoodReads','MissingReads'))as A1,

FROM

Table1.qvd ;

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 12:06:34 GMT

Ya it's working qliksus. Once again thanks a lot if i face any problem in this area i'll approach you.

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 12:18:33 GMT

Sure Buddy no problem

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 13:44:43 GMT

Hi Buddy,

- The Following code is working fine.

Set vA1='NoReads';

Set vA2='GoodReads';

Set vA3='MissingReads';

Set vB=45,47;

Table1:

LOAD

A1,

where (A1='$(vA1)' or A1= '$(vA1)' or A1= '$(vA1)' ) and match(B,$(vB);

LOAD

if(A='No Read','NoReads',if(Len(A)>0,'GoodReads','MissingReads'))as A1,

FROM

Table1.qvd ;

Table1:

LOAD

A1,

where (A1='No Read' or A1= 'GoodReads' or A1= MissingReads);

LOAD

if(A='No Read','NoReads',if(Len(A)>0,'GoodReads','MissingReads'))as A1,

FROM

Table1.qvd ;

- But the thing is i had few more fields to put in the condition.

For eg:

where (A1='$(vA1)' or A1= '$(vA1)' or A1= '$(vA1)' ) and match(B,$(vB) and match(C,$(vC) and..........;

- I'm getting the exact result when i use AND to concatenate the condition but if i use OR i'm not getting the exact result.

- If i give empty for one of the variable in above conditon it is not working.

For eg:

Set vB= ' ' ;

Set vC= '';

How can i overcome this buddy....

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 14:15:07 GMT

Give something like

load * from table

where (A1='$(vA1)' or A1= '$(vA1)' or A1= '$(vA1)' ) or ( match(B,$(vB) ) or ( match(C,$(vC) )

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Tue, 19 Jul 2011 14:26:38 GMT

Hi,

As i am leaving office i cant reply today put ur problem i will reply once i am back

How to apply condition for qvd file with multiple values for one field?

jagannalla — Tue, 19 Jul 2011 14:33:48 GMT

ok Qliksus...i'll wait for your solution in mean while i try myself...

Re: How to apply condition for qvd file with multiple values for one field?

qliksus — Wed, 20 Jul 2011 05:47:07 GMT

Did u get the solution

How to apply condition for qvd file with multiple values for one field?

jagannalla — Wed, 20 Jul 2011 05:52:54 GMT

No...

How to apply condition for qvd file with multiple values for one field?

qliksus — Wed, 20 Jul 2011 06:52:04 GMT

ok my question is

1) why did u want to use the OR condition

2) The OR and And will give different result bcoz generally OR will load all the data and AND will filter

some data

How to apply condition for qvd file with multiple values for one field?

jagannalla — Wed, 20 Jul 2011 07:38:51 GMT

Actually i had two qvw files..

one with all selections i can make there.

For eg:

load A,B,C,D,E,F from table.qvd;

1. Now i'll take A,B,C in listbox respectively. So that i can choose my selection from this listbox.

2. To see how ouput comes here, i had taken one table box with all fields.

3. Now the data of table box will change based on selection made in listbox.

In second qvw file now i had taken same table box with all fields but here i'm not taking any selections just i'm showing tablebox.

But i'm passing all selections made in first qvw while so dat i can write where conditon for qvd ...

In Where condtion if i user OR i'm not getting exact output when i compare with output of first qvw file. When I keep AND i'm getting exact result but, in where conditon if any field is compare with empty value i'm facing the problem.

How to apply condition for qvd file with multiple values for one field?

jagannalla — Wed, 20 Jul 2011 11:55:10 GMT

Qliksus,

Did you understood what i said...Is there any solution.