14 Replies Latest reply: Mar 18, 2016 5:07 PM by Paul Tomeo RSS

    Can anyone help tell me why my load script wont work?

    Paul Tomeo

      I am trying to add a section to my load script to help me generate the Canonical Date reference. I followed along with the blog post and I think I have an idea how it should work but when I try to load my data it errors out every time.

       

      What I have tried to load is shown below, any help would be greatly appreciated as I am struggling to understand how the scripting language works within Qlik Sense:

       

      [QLIK report -No Incomplet]:
      LOAD
      [Application Number],
      [First Active Date],
      [First Completed Date],
      [First Withdrawn Date],
      [First Cancelled Date]
       
      FROM [lib://QLIK Data/QLIK_report.xlsx]
      (ooxml, embedded labels, table is [QLIK report -No Incomplet]);

       

      DataBridge:
      Load [Application Number],[First Application Submitted Date] as CanonicalDate,
      Resident DateTable;
            
      Load [Application Number],[First Active Date] as CanonicalDate,
      Resident DateTable;
        
      Load [Application Number],[First Completed Date] as CanonicalDate,
      Resident DateTable;
              

        • Re: Can anyone help tell me why my load script wont work?
          Martin Pohl

          Hello Paul,

           

          is this the complete script?

          You load your data in table [QLIK report -No Incomplet].

          Then you want to reload datas from Date Table (resident Date Table), but in this script there is no table called Date table.

          Also, in first load in your DataBridge, you load the field [First Application Submitted Date], but this field isn't in the [QLIK report -No Incomplet] table.

          Regards

            • Re: Can anyone help tell me why my load script wont work?
              Paul Tomeo

              Well I thought that was all in needed for the script. But I am very new to Qlik Sense, so I still don't have a good idea how these scripts really work...

               

              I thought all I needed to do was add the DataBridge script below my data load script. But I guess I am not understanding how these function properly.

               

              I am just trying to create a common date field so that I can report the number of project applications submitted, number of projects active, and number of projects completed on a given day.

            • Re: Can anyone help tell me why my load script wont work?
              sujith madhavan

              [DateTable]:
              LOAD
              [Application Number],
              [First Active Date],

              [First Application Submitted Date],
              [First Completed Date],
              [First Withdrawn Date],
              [First Cancelled Date]
               
              FROM [lib://QLIK Data/QLIK_report.xlsx]
              (ooxml, embedded labels, table is [QLIK report -No Incomplet]);

               

              DataBridge:

              Load [Application Number],[First Application Submitted Date] as CanonicalDate

              Resident DateTable;

                    

              Load [Application Number],[First Active Date] as CanonicalDate

              Resident DateTable;

                

              Load [Application Number],[First Completed Date] as CanonicalDate

              Resident DateTable;

                • Re: Can anyone help tell me why my load script wont work?
                  Paul Tomeo

                  Ok so that seems to work better although it says that it resulted in 1 synthetic key, should I be worried about this?

                   

                  $Syn 1 = Application Number+First Application Submitted Date+First Active Date+First Completed Date

                   


                  I want to understand why this worked. So from what I gathered the first line of the script will create a new table, correct? So before I was creating a table that was already contained in the data set, which is why you changed this to [DataTable]?

                   

                  The next step is to call out (LOAD) which variables you want to use from the given data set, which is why I need to load the dates into the new table.

                   

                  Then I am creating another table called DataBridge in which I am associating the dates together under the new variable CanonicalDate.

                   

                  Am I understanding this correctly? I am coming from an excel based world so this scripting/coding is very foreign to me...

                   

                  Thanks for your help!!

                    • Re: Can anyone help tell me why my load script wont work?
                      jagan mohan rao appala

                      HI,

                      Try this

                       

                      [DateTable]:
                      LOAD
                      [Application Number],
                      [First Active Date],

                      [First Application Submitted Date],
                      [First Completed Date],
                      [First Withdrawn Date],
                      [First Cancelled Date]
                       
                      FROM [lib://QLIK Data/QLIK_report.xlsx]
                      (ooxml, embedded labels, table is [QLIK report -No Incomplet]);

                       

                      DataBridge:

                      Load [Application Number],[First Application Submitted Date] as CanonicalDate

                      Resident DateTable;

                       

                      Concatenate(DataBridge)

                      Load [Application Number],[First Active Date] as CanonicalDate

                      Resident DateTable;

                       

                      Concatenate(DataBridge)

                      Load [Application Number],[First Completed Date] as CanonicalDate

                      Resident DateTable;

                       

                      For this script you won't get Synthetic keys.

                       

                      Regards,

                      Jagan.

                        • Re: Can anyone help tell me why my load script wont work?
                          Paul Tomeo

                          Hi Jagan,

                           

                          This still gives me a Synthetic Key, any thoughts as to why?

                           

                          $Syn 1 = Application Number+First Active Date+First Application Submitted Date+First Completed Date+First Withdrawn Date+First Cancelled Date

                           

                          I am also not sure that the CanonicalDate is functioning correctly, which I guess could possibly be because of the synth key?

                           

                          Like I stated previously my objective is to have a bar graph with a common timeline on the X axis, while having different measures on the Y axis. For this example I would want to see number of application submitted on a given day as well as number of completed applications for the same given date. Understanding that the applications process takes many weeks to go from the submitted status to the complete status.

                           

                          When I create these graph separately using first application submit date as the dimension I see for 1/11/16 we received 38 applications, when looking at first completed date as the dimension I see that we completed 76 applications on 1/11/16.

                           

                          This is what I expect to return when using the CanonicalDate dimension as well, but I am not sure I am setting the measure properly anymore... as it is returning combined numbers rather than separating count out by measure (App Submitted & App Completed). I used the following expression for the measure: "Count([First Application Submitted Date])" figuring that this would give me a count of the applications with the [First Application Submitted Date] being equal to 1/11/16 (or the date in which the app was submitted on). But it does not return the 38 in which I expect it to. When I set the measure to be something like "Count([CEC-AC])" What it reports is the combined number of apps submitted on 1/11/16 and the number of application completed on 1/11/16 for a total of 114. But I prefer to see these values separately rather than in total, but I am failing to grasp how to do that.

                    • Re: Can anyone help tell me why my load script wont work?
                      Nizam HM

                      try to avoid comma in ur table table before resident thats the problem

                      • Re: Can anyone help tell me why my load script wont work?
                        Gabor Tarnoczai

                        Hi Paul,

                         

                        As I see you have an extra comma ' , ' at the last columns of resident tables.

                         

                        DataBridge:

                        Load [Application Number],[First Application Submitted Date] as CanonicalDate ,  <-this is the extra comma

                        Resident DateTable;


                        G.

                        • Re: Can anyone help tell me why my load script wont work?
                          Sangram Reddy

                          Hi Paul,

                           

                          Get rid of the ',' to the end of the last field in the load statements.

                           

                          Thanks,

                          Sangram.