topic expression not working in QlikView

expression not working

Anonymous — Mon, 13 Jun 2011 18:48:23 GMT

Hi,

I have the following expression

=Sum({$<FV_GROUP = {'FV'}, FORMID = {'IP'}>} [Total])

FV_GROUP is defined as

med:
LOAD
 'Med_rec' AS record_type,
 MEM#,
 //MEM# & FORMID AS MEDFORMMED, 
 //MEM# as Med_MEM#,
 //MEM# as Medd, 
 ENCOUNTER#,
     '3rd Quarter 2011' as Period, //Create a column calls period and populate it with the 3rd text //
     '261740' AS MM,
     '1.26' AS ACG,
     [VEND NAM], 
     [TAX ID], 
     [TOTAL PAID] as Total, 
     SERVICE, 
     DISCHARGE, 
     LOS,
     [ICD9 DIAG], 
     [FORM TYPE],
     //IF([FORM TYPE]='HCFA 1450','H',If([FORM TYPE]='HCFA 1500','P',If([FORM TYPE]='NARRATIVE',' ','N'))) AS FORMID_MED,
      IF([FORM TYPE]='HCFA 1450' AND LOS >=1,'IH',IF([FORM TYPE]='HCFA 1450' AND LOS =' ','OH',If([FORM TYPE]='HCFA 1500','P',If([FORM TYPE]='NARRATIVE',' ','N')))) AS FORMID,
      IF ([TAX ID]='~','NFV','FV') AS FV_GROUP
     
FROM
Data\Dimensions\med.qvd
(qvd);

I am getting a 0 as a total

What am I doing wrong?

Thanks.

Re: expression not working

IAMDV — Tue, 14 Jun 2011 10:37:28 GMT

Please can upload the sample QV document with the expression and what would be expected result? It will be easy to solve while testing with the data.

Cheers - DV

expression not working

Tue, 14 Jun 2011 12:53:16 GMT

Hi there,

for my oppinion it doesn't depend on the field "FV_GROUP", better take a look at "FORMID":

IF([FORM TYPE]='HCFA 1450' AND LOS >=1,'IH',

IF([FORM TYPE]='HCFA 1450' AND LOS =' ','OH',

If([FORM TYPE]='HCFA 1500','P',

If([FORM TYPE]='NARRATIVE',' ','N')))) AS FORMID,

Check the values of your field FORMID. I am sure you won't find "IP".

Regards, Roland

Re: expression not working

Anonymous — Tue, 14 Jun 2011 13:39:53 GMT

Hi Roland,

You are correct, I made a mistake. I changed the FORMID to IH. I suspect that the IF statement for the FV_GROUP is not right.

I just want to check the TAX ID. If TAX ID = '~' then I want the FV_GROUP to be 'NFV'. If the TAX ID is anything else then I want FV_GROUP to be 'FV'

There are only 6 TAX ID. They are as follow:

41-099

20-599

20-833

41-176

72-158

Once the FV_GROUP is created then I want to use it an an expression in a chart to show total for each FV_GROUP. I hope this makes sense.

Thanks.

Re: expression not working

Tue, 14 Jun 2011 14:01:56 GMT

Hello hsm,

I tested your exam data and QV worked fine (as I expected). see attached qvw

Seems you have some trouble with your data quality within the "TAX ID"-field. I would check for leading / trailing blanks of the "~" because you are filtering on an exact text-string (this is "~" and not for ex. " ~").

If you found it out, possible workarounds may be using Trim(TAX_ID) or Wildmatch(... '*~*')

Regards, Roland

Re: expression not working

Anonymous — Tue, 14 Jun 2011 14:08:21 GMT

Hi Roland,

I cannot open the qvw because I still have the personal edition. I am wondering if you can post the code? thanks for your help.

Re: expression not working

Tue, 14 Jun 2011 14:22:28 GMT

Hi again, that's all:

RawData:

LOAD * Inline [

TAX ID

41-099

20-599

20-833

41-176

72-158

];

Result:

LOAD

IF ([TAX ID]='~','NFV','FV') AS FV_GROUP,

[TAX ID] AS ResTaxID

Resident RawData;