Load exact patitioned QVD-file in ODAG

[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]

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.