topic Re: How to load Inline Date and associate to a Field in QlikView

How to load Inline Date and associate to a Field

Anonymous — Wed, 16 Sep 2015 22:09:22 GMT

Hi Qlikview Experts,

Was adopting what I learned here:

One of the responses had an attachment that showed how to do this; however, it was loaded as an island. I want to load dates and associate them to specific line items of another table; however, I am having challenges correctly modifying the script to do this.

So in my example, I have a loaded table that looks like this:

ID	Data
Record1	1500
Record2	2000
Record3	500
Record4	200

I then want to add a column called, "Date" using the Script. More specifically, I want to attach a Date of "01/15/2015" to Record1 and a Date of "09/25/2016" to Record4. Record2 and Record3 will remain having blank Dates. This is just an example and in my actual document, more than a hundred records are loaded, and I only want to input explicit Dates for about a handful of these records.

Appreciate any help. Thanks in advance.

Re: How to load Inline Date and associate to a Field

Anonymous — Wed, 16 Sep 2015 22:19:51 GMT

To confirm: you are essentially associating 'Data' to 'Date' by its load order or sequence.

Assuming the above is true, using the physical order of two tables as a linkage/join is a risky and could create inconsistent results between reloads and as the data in the source xlsx grows or changes.

However if you do not have another way you can use the RecNo() function to create a common linkable field between the two tables. Note: RecNo() is different and prefered in this situation from RowNbr() because RecNo() returns the row number of the data set before it is loaded, sorted or transformed in anyway.

Here is what your load statement would look like:

Directory;

LOAD ID,

Data,

RecNo() as DateLink

FROM

loadfile2.xlsx

(ooxml, embedded labels, table is Sheet1);

Dates_Temp:

LOAD

Date(Date,'MM/DD/YYYY') AS Date,

RecNo() as DateLink

INLINE [

Date

01/15/2015

09/25/2016

];

Re: How to load Inline Date and associate to a Field

Anonymous — Wed, 16 Sep 2015 22:26:10 GMT

This is actually very helpful (learned something new!), but what if I do not want to load these dates in sequence? So in my original example, I was hoping that 01/15/2015 will be associated to Record1 and 09/25/2016 would be associated to Record4. Is that possible? Using your suggested method above associates the dates to Record1 and Record2 respectively.

Re: How to load Inline Date and associate to a Field

MarcoWedel — Wed, 16 Sep 2015 23:04:18 GMT

Hi,

you have to provide the information which record the dates should be associated with.

Solution could be to load the date table separately using a key field, or to integrate the date field into your initial table using join or applymap:

date table

table1:
LOAD ID,
    Data
FROM [https://community.qlik.com/thread/181055] (html, codepage is 1252, embedded labels, table is @1);

Dates:
LOAD * INLINE [
    ID, Date
    Record1, 01/15/2015
    Record4, 09/25/2016
];

Join

table1:
LOAD ID, 
     Data
FROM [https://community.qlik.com/thread/181055] (html, codepage is 1252, embedded labels, table is @1);

Left Join (table1)
LOAD * INLINE [
    ID, Date
    Record1, 01/15/2015
    Record4, 09/25/2016
];

ApplyMap

mapDates:
Mapping LOAD * INLINE [
    ID, Date
    Record1, 01/15/2015
    Record4, 09/25/2016
];

table1:
LOAD ID, 
     Data,
     ApplyMap('mapDates', ID, Null()) as Date
FROM  [https://community.qlik.com/thread/181055] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco