Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hsm12345
New Contributor

expression not working

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.

1 Solution

Accepted Solutions
Not applicable

Re: expression not working

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

6 Replies
IAMDV
Honored Contributor II

Re: expression not working

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

Not applicable

expression not working

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

hsm12345
New Contributor

Re: expression not working

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.

Not applicable

Re: expression not working

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

hsm12345
New Contributor

Re: expression not working

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.

Not applicable

Re: expression not working

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;

RR

Community Browser