How to load Inline Date and associate to a Field

[Anonymous]

Hi Qlikview Experts,

Was adopting what I learned here:

Inline Date

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.

[Anonymous]

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

];

[Anonymous]

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.

[MarcoWedel]

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