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: 
Not applicable

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

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;
        

14 Replies
martinpohl
Partner - Master
Partner - Master

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

Not applicable
Author

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.

sujith1011
Partner - Creator
Partner - Creator

[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;

Not applicable
Author

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!!

jagan
Luminary Alumni
Luminary Alumni

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.

nizamsha
Specialist II
Specialist II

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

Not applicable
Author

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.

Not applicable
Author

Hi Nizam,

I am not sure I understand what you are trying to say. Would you be able to clarify your comment?

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach the whole script?  For the script suggested you won't get any Synthetic key, I think you missed to add some script.

Regards,

jagan.