Online Safety Community

Introduction

I was recently at a client site when a conversation about data typing in QlikView Online Training began. It involved the client noticing that a listbox they had placed on an application was displaying dates both left aligned and right aligned. What had caused this?

What’s Happening?


Most programming languages have well defined data types: Int, String, Decimal, Double, Array, and so on. Most relational databases also have the same for the fields you define within a table. Other products also guess at a type: ever had Excel convert something to a String when a Number was wanted? QlikView does not have a defined type that a developer can use but they do internally make use of two.

In order for QlikView to be able to play nicely with all the places from where one would want to load data, it abandoned the formal definition of types within the load script, data model and user interface. I can see programmers all over the world begin to cringe at the thought of no type safety, but have no fear: this is what allows the connecting of fields by name in the associative data model – a data model that may have differing underlying types. This also allows the loading of data from Excel, SQL Server and Oracle all within the same application and without any type conversion problems.

It is a very common occurrence to load data from a CSV file, Excel file, QVD file and a SQL Server database into a QlikView application. If type safety were needed, the few hundred lines of load script needed for that would be significantly higher and development would be slowed down. There would also be more errors during the casting and conversion processes. In order to keep the creation of a QlikView application simple – from loading the data to using it – QlikView uses interpretation. It only internally stores and uses two data types: String and Number. If a field cannot be interpreted as a Number, only the string value will ever be used. When loading data from a QVD, QlikView does not interpret the data because it already was interpreted during the QVD’s creation.

When data is loaded all data will come in as a string. QlikView will ALSO try to interpret it as a Number if it can during the loading of the field. It will keep track and make use of the appropriate type based on how that field is used in the document.

There is a built in function ( [dual(s, n)] ) that can be used while loading a field within the loadscript. The “s” parameter is the string or display value for field and the “n” parameter is the numeric value for field. This allows a developer the chance to use a different type for either the display or numeric usage of the field.

One other advantage of QlikView not having a data type is apparent during calls to a function. In most cases the calls are simply of the form [function_name(field)]. If the function requires a String, then QlikView uses the string value for the field. If the function requires a Number then the number values for the field is sent.

Conclusion


In the case of the customer mentioned earlier, it was clear that the date being read in was being interpreted as a string in one case and couldn’t be converted to a number, where as other dates were being read in as a string but were able to be converted to a number. Since QlikView has no problem displaying both Numbers and Strings in a listbox, it happily displayed the data both ways.

I hope this article helps you understand how QlikView is not only able to load data from multiple source systems but also maps a display and a numeric value for each field loaded.

Update:

I am updating this article because I encountered a similar situation to this again and I wanted to share the solution.

Recently I was needing to import data into QV from a TSV file that was created from an Access Database export. I needed to import a field that was of the format ‘MM/DD/YYYY hh:mm:ss’ and I needed QV to be able to store this properly as a  dual(s, n) as it normally would when encountering a date field. However I always ended up with a NULL value. The following statement worked on this field.

date(floor(date#([Old Date],'MM/DD/YYYY hh:mm:ss')),'MM/DD/YYYY') as [New Date]

That statement allowed me to have a properly formatted string for display (without time stamp) and for easy date math with the numeric value.

Source: resultdata

Views: 44

Reply to This

Take our poll!

Take our poll!

Latest Activity

Manavalan posted an event
Thumbnail

Nebosh course in Chennai at Chennai

July 17, 2018 at 6pm to July 31, 2018 at 7pm
2 hours ago
Roger Steven posted events
3 hours ago
Training Doyens posted an event
Thumbnail

Reward and Recognition for Small Companies: How to Boost Productivity And Keep Your Employees Motivated at 26468 E Walker Dr, Aurora, Colorado 80016

August 9, 2018 from 1pm to 2pm
OVERVIEWThis webinar will cover the various components and steps of effective reward and recognition in small companies. We will cover some theories of motivation; the neurophysiology of recognition and its effect on productivity; the timing of recognition who should do the recognition; and methods of…See More
3 hours ago
Profile IconAlice Denny, Joseph Adami and Traininng.com LLC joined Online Safety Community
Friday

Forum

5 TECHNOLOGY-BASED LONE WORKER SAFETY SOLUTIONS

ABOUT 75% OF EMPLOYEES IN NORTH AMERICA ARE MOBILE WORKERS. ADVANCES IN COMMUNICATIONS TECHNOLOGY MEANS THESE WORKERS CAN WORK ANYWHERE AT ANY TIME. THESE NEW TECHNOLOGIES ALSO MEAN THESE MOBILE…Continue

Tags: Solutions, People, IoT, Monitoring, Remote

Started by Jen McDade May 31.

Road Safety Solutions 14 Replies

The Road Safety Signs ,Barriers,Humps,Hazard Markers and Visual Warnings are some of the important marks to be observed. Signs such as "keep left",stop, "give way" should not be casually treated.…Continue

Tags: safety, gear, wear, Equipment, &

Started by Enna Henry. Last reply by Jen McDade May 31.

Remote Monitoring

Get "Safe Assets and Sound Productivity" Through Remote Monitoring.Visit:…Continue

Tags: Solutions, People, IoT, Monitoring, Remote

Started by Jen McDade May 23.

Python Condition Objects Tutorial in 2018 1 Reply

If you have knowledge of other programming languages, then you would know the importance of conditional statements. Conditional statements are required for taking decisions. Whenever we operate the…Continue

Tags: course, certification, training, languages, programming

Started by Elena Lauren. Last reply by Jim Chesters May 15.

Power BI Visualization Types

Visualizations in Power BI displays the visual insights from a data. In power bi service a visual can be pinned from reports to create dashboards. Visuals are used in reports.List of visualizations…Continue

Tags: COURSE, TRAINING, BI, POWER

Started by Azharuddin May 15.

Badge

Loading…

© 2018   Created by Safety Community.   Powered by

Badges  |  Report an Issue  |  Terms of Service