9 Replies Latest reply: Jun 19, 2017 11:44 AM by Brien McHale RSS

    Set Analysis Not Equal to 0

    Brien McHale

      I have the below expression, but not coming with any results and feel it has something to do with 'not equal'.

       

      This works:

       

      Num(sum({<[DateType] = {"Fundings"} >} Loan_Amount),'$') 

       

       

      This doesn't:

       

      Num(sum({<[DateType] = {"Fundings"}, Cancellation_Date -={'0'} >} Loan_Amount),'$') 

       

      and I need to exclude Amounts when Canonical date is null or is equal to 0, it appears to be =0 in the data.

       

      Any help would be appreciated, thanks.

        • Re: Set Analysis Not Equal to 0
          Vishwarath Nagaraju

          Try this may be:

           

          Num(sum({<[DateType] = {"Fundings"} > +  < Cancellation_Date -= {'0'}  >} Loan_Amount),'$')


          OR

          Not sure about below, you can try?

          Num(sum({<[DateType] = {"Fundings"} > +  < Cancellation_Date -= {"= $(= Len(Trim(Cancellation_Date)) = 0)"}  >} Loan_Amount),'$')

            • Re: Set Analysis Not Equal to 0
              Brien McHale

              Capture.PNG

               

              This one accepted, but still not excluding and apologies I meant:  Num(sum({<[DateType] = {"Fundings"} > + < CanonicalDate -= {'0'} >} Loan_Amount),'$')

              My datetype is essentially looking at lead date, registration date, and close date to build canonical date and then I concatenated this into actuals table and am trying to see actual fundings only if canoncical date has been populated with actual date.

               

               

              SET ThousandSep=',';
              SET DecimalSep='.';
              SET MoneyThousandSep=',';
              SET MoneyDecimalSep='.';
              SET MoneyFormat='$#,##0.00;($#,##0.00)';
              SET TimeFormat='h:mm:ss TT';
              SET DateFormat='M/D/YYYY';
              SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

              Temp_Actuals:
              LOAD Lead_Number,
              Lead_Create_Date,
              Application_Date,
              Loan_Amount,
              Registration_Date,
              Closing_Date,
              Cancellation_Date,
              Denial_Date,
              Disbursement_Occurred_Date,
              Property_City,
              Property_State,
              Property_Zip_Code,
              Loan_Officer_Name,
              PHH_Loan_Officer_ID,
              Employee_Active_Indicator,
              Loan_Officer_ID,
              Sales_Manager,
              Referring_Employee,
              Referring_Employee_ID,
              Branch_Number,
              Branch_Name
              Purchase_Or_Refi,
              Loan_Purpose_Code,
              Loan_Status,
              UW_Status,
              First_Approval_Date,
              Approved_Date,
              PHH_STAT_CDE,
              Underwriting_Submission_Date,
              Discount_Program,
              Program_Number,
              Program_Description,
              Lock_Expiration_Date,
              Processor_Name,
              Assistance_Provider_1,
              Assistance_Amount_1,
              Assistance_Provider_2,
              Assistance_Amount_2,
              Appraisal_Received_Date,
              Appraisal_2_Recieved_Date,
              Scheduled_Closing_Date,
              Closing_Package_Received_Date,
              Note_Recieved_Date,
              Appraisal_Inspection_Date,
              Appraisal_Confirmed_Date,
              Credit_Contingency_Date,
              Closed_Ind,
              Registration_Ind,
              Cancelled_Ind,
              Referral_Ind,
              Lock_Expiration_Status,
              Product_Category,
              Product_Segmentation,
              Private_Banking_Ind,
              Day_Count_Reg_Closing,
              Day_Count_Lead_Reg,
              Lead_Count,
              Loan_Count,
              Extract_Date,
              Num_Days_Lock_Expiration,
              Loan_Program_Code,
              Acct_Sold_Status,
              Foreign_National_Indicator,
              Self_Sourced,
              Lead_Status_Code,
              APPLICATION_STATUS,
              Level_Product_Code_2,
              Level_Product_Code_3,
              Program_Type,
              LOCK_DT_TM,
              Loan_ID,
              Specialized_Sales_Manager,
              Sales_Area,
              Mortgage_Consultant,
              Sales_ID,
              Employee_ID,
              if(Region='MORTGAGE - CRA', 'CRA',
              if(Region='NORTHERN CALIFORNIA AREA' , 'NORTHERN CALIFORNIA EAST & WA MKT',
              if(Region='PRIVATE BANKING' , 'PB', Region
              )))
              as Region





              FROM
              [\\qlikviewnau.us.hsbc\Content\US RBWM\Sources\AOP Source\AOP_ExtractvQlik.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);





              Temp_DateBridge:
              LOAD  Lead_Number,Lead_Create_Date as CanonicalDate, 'Leads' as DateType
              Resident Temp_Actuals;

              Concatenate(Temp_DateBridge)
              LOAD Lead_Number,Registration_Date as CanonicalDate, 'Registrations' as DateType
              Resident  Temp_Actuals;
              Concatenate(Temp_DateBridge)
              LOAD Lead_Number,Closing_Date as CanonicalDate, 'Fundings' as DateType
              Resident  Temp_Actuals;



              Temp2_Actuals:
              NoConcatenate
              Load * resident  Temp_Actuals;
              join  Load * resident Temp_DateBridge;

              drop table Temp_Actuals;
              drop table Temp_DateBridge;


              Actuals:
              NoConcatenate Load *,
              Region&'-'&Product_Category&'-'&DateType&'-'&month(CanonicalDate)&'-'&Year(CanonicalDate) as AreaTargetKey,
              Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID&'-'&DateType&'-'&month(CanonicalDate)&'-'&Year(CanonicalDate) as LOTargetKey
              resident Temp2_Actuals;

              drop table Temp2_Actuals;

               

            • Re: Set Analysis Not Equal to 0
              tripati rajana

              Hi,

               

               

              Try like this

               

              Num(sum({<[DateType] = {"Fundings"}, Cancellation_Date -={} >} Loan_Amount),'$')



              • Re: Set Analysis Not Equal to 0
                Sunny Talwar

                If it is =0 in the data... this should work

                Sum({<[DateType] = {"Fundings"}, Cancellation_Date -={'0'} >} Loan_Amount)

                 

                I wonder why it doesn't. Would you be able to share a sample where this isn't working?