Solved: Load exact patitioned QVD-file in ODAG - Qlik Community

AlexWest

Hi, everyone!
My last question was about making partitions from one QVD to several smaller.
And kind user Brunobertels sent me a link to a post, how to do automatic partitions.

And now, I have many pratitioned QVD-files and task:
I have to load those QVD-files which have only data by asked dates in ODAG request.

I just made a next script:

//first check if it's not an ODAG request, but a common load from current window
if '$(odagActive)' = '' then
set 'odag_Date' = '''01.07.2019''';
end if;

[Tasks]:
LOAD
TaskID,
ContactID,
CompanyID,
CreationDate,
TaskExpDate,
EditDate,
TaskStatus,
CreatedUserID,
ResponsibleUserID,
TaskName
FROM[lib://DataFiles/CRM_Tasks_*.qvd](qvd)
where Match(CreationDate,$(odag_Date));

But as you can see, it's a bad script, coz it loads all the files(((
Please help me to load exact files with dates which was requested.

Here's my QVDs:

lib://DataFiles/CRM_Tasks_m01_y2019.qvd
lib://DataFiles/CRM_Tasks_m02_y2019.qvd
lib://DataFiles/CRM_Tasks_m03_y2019.qvd
lib://DataFiles/CRM_Tasks_m04_y2019.qvd
lib://DataFiles/CRM_Tasks_m05_y2019.qvd
etc till the end of 2020.

steeefan

Let's assume your date selection string is formatted as this when selecting more than one date: '01.08.2019','01.09.2019','01.10.2019'. This code would then go over every date and fetch the relevant data:

// Initialize results table
Tasks:
NOCONCATENATE LOAD * INLINE [
  TaskID
];

// Testing only
SET odag_Date = '''01.08.2019'',''01.09.2019'',''01.10.2019''';

// Iterate over each date
FOR EACH vDate IN $(odag_Date)

  TRACE Working on ODAG date $(vDate);

  // Create values to select relevant QVD file
  LET vODAGDate = Num(Date(Date#('$(vDate)', 'DD.MM.YYYY')));
  LET vQVDMonth = Num(Month('$(vODAGDate)'));
  LET vQVDMonth = If($(vQVDMonth) < 10, '0', '') & $(vQVDMonth);
  LET vQVDYear = Year($(vODAGDate));

  LET vQVDFile = 'CRM_Tasks_m$(vQVDMonth)_y$(vQVDYear).qvd';
  TRACE $(vQVDFile);

  // Optimized load w/o filtering from relevant QVD file
  Tasks_stg1:
  LOAD
    //..
  FROM
    [lib://DataFiles/$(vQVDFile)]
    (qvd);

  // Filtered load on data just loaded
  // Add to results table
  CONCATENATE (Tasks) LOAD
    //..
  RESIDENT
      Tasks_stg1
  WHERE
      Match(CreationDate, $(odag_Date));
  
  DROP TABLE Tasks_stg1;

NEXT vDate;

View solution in original post

steeefan

There's multiple ways of doing this. One possible solution is to extract the month and year part from odag_date and then only load from the file that would correspond to the result.

set odag_Date = '01.07.2019';

LET vODAGDate = Num(Date(Date#($(odag_Date), 'DD.MM.YYYY')));
LET vQVDMonth = Num(Month('$(vODAGDate)'));
LET vQVDMonth = If($(vQVDMonth) < 10, '0', '') & $(vQVDMonth);
LET vQVDYear = Year($(vODAGDate));

LET vQVDFile = 'CRM_Tasks_m$(vQVDMonth)_y$(vQVDYear).qvd';

Then load from that file only:

Tasks_stg1:
LOAD
  //..
FROM
  [lib://DataFiles/$(vQVDFile)]
  (qvd);

Tasks:
NOCONCATENATE LOAD
  //..
RESIDENT
  Tasks_stg1
WHERE
  Match(CreationDate, $(odag_Date));

DROP TABLE Tasks_stg1;

AlexWest

Hi!
Thanks for the answer!
I should write the script in that Template App only?

steeefan

Yes, that would be in the ODAG template app, where you had the script you posted originally.

AlexWest

thanks a lot, kind Steeefan! It's exactly what I needed))) I will save your script as a golden one))
But I have a last little question:
why do we use an extra DATE-function in the next row?

LET vODAGDate = Num(Date(Date#($(odag_Date), 'DD.MM.YYYY')));

I mean, I thought that the expression could work without the DATE-func. Am I wrong?

AlexWest

Oh, sorry Steeefan, but this script doesn't allow me to get dates more than one in the Selection App. It's error appears. It works only with one date.
I tried to use a FOR-EACH function, but I can't get an 'odag_Date' field. If write

set odag_Date = '03.04.2019';

for Each i in FieldValueList('odag_Date')
trace $(i);
LET vODAGDate = Num(date(Date#($(i), 'DD.MM.YYYY')));
LET vQVDMonth = Num(Month('$(vODAGDate)'));
LET vQVDMonth = If($(vQVDMonth) < 10, '0', '') & $(vQVDMonth);
LET vQVDYear = Year($(vODAGDate));

LET vQVDFile = 'CRM_Tasks_y$(vQVDYear)_m$(vQVDMonth).qvd';

Tmp_Tasks:
LOAD
fields
FROM
[lib://DataFiles/$(vQVDFile)]
(qvd)
WHERE
Match(CreationDate, $(odag_Date));

next;

Qlik says this:

The following error occurred:

Field 'odag_Date' not found

The error occurred here:

for Each i in FieldValueList('odag_Date')

Could you please help to modify it that I could get several dates and generate an App?

Excuse me please))

steeefan

FieldValueList() requires a field name from a table as argument, not a variable.

In what format does the template app receive the date selection string if more than one date is selected?

Regarding your question "why do we use an extra DATE-function in the next row?": That's only a habit.

AlexWest

Hi, Steeefan!
I'm afraid I didn't get it correct about format, because I'm so newbie in ODAG theme.
In a Selection App I just have dates 'DD.MM.YYYY', the expression in a Link counts distinct Dates.

And the script in the Template App is your script 🙂

set odag_Date = '01.07.2019';

LET vODAGDate = Num(Date(Date#($(odag_Date), 'DD.MM.YYYY')));
LET vQVDMonth = Num(Month('$(vODAGDate)'));
LET vQVDMonth = If($(vQVDMonth) < 10, '0', '') & $(vQVDMonth);
LET vQVDYear = Year($(vODAGDate));

LET vQVDFile = 'CRM_Tasks_m$(vQVDMonth)_y$(vQVDYear).qvd';

Tasks_stg1:
LOAD
  //..
FROM
  [lib://DataFiles/$(vQVDFile)]
  (qvd);

Tasks:
NOCONCATENATE LOAD
  //..
RESIDENT
  Tasks_stg1
WHERE
  Match(CreationDate, $(odag_Date));

DROP TABLE Tasks_stg1;

steeefan

Please do not accept any other replies as a solution than those that actually are.

In the ODAG demo app I once built, I after the reload of the ODAG template app, I have these variables values, created by the ODAG selection script provided by Qlik:

Can you confirm that you have similiar variables in your template app after generating?

AlexWest

If you saying about Generated App, I can see this

It's only one Date I can choose in the Selection App.
I mean, there are many dates on your screen, I can't do that because it's error happens(

This is what I see in the Selection App where I have only Calendar.

But if I choose only one date it works good.

Load exact patitioned QVD-file in ODAG

Qlik Sense

Script