Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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.
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:
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
];
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
];
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