I have a Qlik process with multiple steps (workspaces). The first is to import data from external sources, the second is to transform it, if necessary. I need to modify dates in the transform step in order to avoid date conflicts for a time-based index of standard values, but the issue I'm facing is that the dates to be compared are on different records. I cannot modify the process creating the data I'm importing.
My (simplified) import step pulls information where there are standard numerical values for a time period. Example:
Location, Start Date, End Date, Amount
A,01/01/2015,01/01/2015,3
A,01/01/2015,,4
B,08/14/2015,08/20/2015,5
B,08/20/2015,11/18/2015,6
B,11/18/2015,,7
C,06/01/2013,,2
The imported data has, for the same Location, the same Start Date as another record's End Date whenever there is a change in value. The current record will always have a null as the End Date. If I load the data as-is, then I'm going to have two different Amounts for a single Location on conflicting Start/End Dates (see records 3 vs 4 for 08/20/2015, and 4 vs 5 for 11/18/2015).
I cannot think of a simple way to modify my dates in the Load Script to account for these conflicts. Subtracting a day from the End Date field will render some single-day changes as if they never occurred. Adding a day to the Start Dates on records that do not have End Dates might cause issues due to records I'm joining with that have data on the day being pushed forward.
Ideally, I'd like to have the example data mirror the set below:
Location, Start Date, End Date, Amount
A,01/01/2015,01/01/2015,3
A,01/02/2015,,4
B,08/14/2015,08/20/2015,5
B,08/21/2015,11/18/2015,6
B,11/19/2015,,7
C,06/01/2013,,2
However, this is all dependent on information not contained in the record being modified, but would be dependent on if the record is an original record for a Location. This can't be determined until the rest of the dataset is examined. I've never done this in Qlik. Is there something which does this easily? If there is no simple solution, please just let me know that there is no simple solution rather than provide a complex answer. I'd rather not waste anyone's time if there isn't a simple solution.
Thank you in advance,
Greg
