14 Replies Latest reply: Feb 3, 2014 5:12 AM by Julio Lourenco RSS

    Filling blank fields with dates

      People, I have a problem in my database that I have a field called "[Claim Closed Date]", that sometimes it comes blank for me. I would like to create a rule for that, this rule has to be like this: I have another field called "[Status Claim]" and another one called "[Submitted Date]", so, I would like to put like this in the script:

      ...

       

      [Claim Closed Date],

      [Status Claim],

      [Submitted Date],

       

      ....

      if([Claim Closed Date] = "Blank (?)" and [Status Claim] = 'Closed', FILL WITH [Submitted Date] + "30 days",,)

       

      how do I do such thing in the script??

        • Re: Filling blank fields with dates
          Vishnuram Jayaraman

          Hi Julio,

          Try this

          if(isnull([Claim Closed Date])  and [Status Claim] = 'Closed',  Date([Submitted Date] + 30)) as EXPECTED

          • Re: Filling blank fields with dates
            Bill Markham

            if( isnull([Claim Closed Date]) and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

              • Re: Filling blank fields with dates
                Bill Markham

                Julio

                 

                This will put the data into a field called [Claim Closed Date]

                 

                if( isnull([Claim Closed Date]) and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,

                • Re: Filling blank fields with dates

                  It doesn't allow me to have two fields with the same name in this case...

                    • Re: Filling blank fields with dates
                      Bill Markham

                      Julio

                       

                      Then get rid of the original one and just leave the new one.

                        • Re: Filling blank fields with dates

                          i did, it worked, but brings for me no data on this field now...

                            • Re: Filling blank fields with dates
                              Bill Markham

                              Julio

                               

                              Can you paste in your load script.

                                • Re: Filling blank fields with dates
                                  Bill Markham

                                  Julio

                                   

                                  Also could you try it with the suggestion of Massimo Grossi

                                   

                                  if( len(trim( [Claim Closed Date] ))=0 and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,


                                  Mine is searching for Null's, but Massimo's is searching for where the string has length of zero having trimmed of leading & trailing spaces. - which may well be what you need.



                                    • Re: Filling blank fields with dates

                                      Thank you Bill, alot!!!!

                                        • Re: Filling blank fields with dates

                                          Guys, sorry, my mistake, it did not worked correctly. It brought only the ones that were in blank before, not everything... my script is:

                                           

                                          eParts:

                                           

                                           

                                          LOAD [Claim #],

                                               [Work Order #],

                                               [Aircraft Model],

                                               [Serial Number],

                                               Operator,

                                               [Shop Name]  as [Shop Name Claim],

                                               [Shop Code],

                                               [Claim Opened Date],

                                               //([Claim Close Date]),

                                               [Arrival Date],

                                               [Service Complete Date],

                                               [A/C Flight Hours],

                                               [A/C Cycle],

                                               Currency,

                                               [Claim Total],

                                               [Total Approved],

                                               [Total Denied],

                                               [Status Claim],

                                               [Total Scheduled Labor],

                                               [Total Unscheduled Labor],

                                               [Total Service Bulletin Labor],

                                               [Total Other Labor],

                                               [Expendable Parts],

                                               [Total Handling Fee],

                                               [Total Freight],

                                               [Total Miscellaneous],

                                               PAYER,

                                               [Notification #],

                                               [Service Order #],

                                               [Purchase Requisition #],

                                               [PO #],

                                               [PO Date],

                                               //Comments as Comments_eParts,

                                               [PO Total],

                                               [Squawk Item],

                                               [Ref. Item],

                                               Type,

                                               Status,

                                               Date,

                                               [Squawk Total],

                                               [Failure Date],

                                               [Type (Inspection)],

                                               [Source / Discrepancy],

                                               [Description / Corrective Action],

                                               [Man Hours],

                                               [Labor Rate],

                                               [Labor History],

                                              // [Approved? Y / N],

                                              // Comment as Comment_eParts,

                                               Classification,

                                               PO#,

                                               [PN Installed],

                                               [PN Installed Description],

                                               [S/N Installed],

                                               Qty,

                                               [Unit of Measure],

                                               [Your Price],

                                               [List Price],

                                               [Handling Fee],

                                               [Total Handlling],

                                               [CAP / Freight],

                                               [PN Removed],

                                               [SN Removed],

                                            //   [Approved? Y / N],

                                             //  Comment as Comment1_eParts,

                                               Freight,

                                               [Freigh Total],

                                              // [Approved? Y / N],

                                              // Comments as Comments1_eParts,

                                               Description,

                                               //[Total Miscellaneous],

                                             //  [Approved? Y/N],

                                              // Comment as Comment2_eParts,

                                               [Total Amount],

                                               [Sales Org.],

                                              

                                               if( len(trim([Claim Close Date]))=0 and [Status Claim] = 'Closed', date( [Claim Opened Date] + 60,[Claim Close Date])) as [Claim Close Date],

                                              

                                               

                                            IF([Labor Rate]>200,[Labor Rate],) as Wrong_Labor_Rates,

                                           

                                            Month([Claim Close Date]) as MONTH,

                                            Year([Claim Close Date]) as YEAR,

                                            Day([Claim Close Date]) as DAY,

                                            FileName() as DATA_SOURCE,

                                           

                                            IF([Claim #]=PREVIOUS([Claim #]),'0','1') as UNIQUE_FLAG

                                              

                                          FROM

                                          C:\Users\jcloure\Desktop\Feedbackserver_Qlikview\CLAIM_2013_27012014092318.csv

                                          (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                            • Re: Filling blank fields with dates
                              Massimo Grossi

                              if( len(trim( [Claim Closed Date] ))=0 and [Status Claim] = 'Closed', date( [Submitted Date] + 30 ) as [Claim Closed Date] ,