Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Author

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

View solution in original post

6 Replies
IAMDV
Luminary Alumni
Luminary Alumni

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
Author

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

Anonymous
Not applicable
Author

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
Author

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

Anonymous
Not applicable
Author

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
Author

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