topic Re: Create Multiple QVDs from the YearMonth field in App Development

Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 12:45:12 GMT

Hello,

I'm trying to create separate Qvds from the Year/Month field.I'm doing this due to the size of the information to use in qliksense later.

OLEDB CONNECT TO xxxxx

SET QVD Directory = '$(QVD path.txt)';

OriginalTable:
SQL SELECTION*
FROM table1 WHERE CAST(dataFont) >= '2023-05-01'; //

**this "dataFont" field is configured to bring year-month-day and time**

Grouped Table:
TO LOAD
*,
Month(Timestamp(dataFont)) AS Month,
Year(timestamp(dataFont)) AS Year
RESIDENTOriginalTable;

FOR Each YearMonth IN FieldValueList('Year' & Month)
LET tablex = 'tabelax_' & YearMonth & '.qvd';
STORE Grouped Table
INTO '$(QVD Directory)$(tabelax)';

NEXT

DISCONNECT;

//DROP TABLEOriginalTable;
//DROP TABLEGroupedTable;

 

 

When generating the qvd, it only brings the accumulated result for the year. I need you to bring the accumulated Years and Months accumulated.

Can anyone help?

Re: Create Multiple QVDs from the YearMonth field

Or — Tue, 12 Sep 2023 12:52:47 GMT

It doesn't look like your loop is actually loading any values - all it does is store the same data into a bunch of differently-named QVDs. You would need to load the relevant data within the loop and then store that.

Re: Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 13:07:37 GMT

I have tried several options to generate a loop, but all the attempts I have made have been insufficient.

Could you help me how to do the looping?

Re: Create Multiple QVDs from the YearMonth field

Or — Tue, 12 Sep 2023 13:14:59 GMT

The issue isn't (necessarily) with the loop structure - it's that you're not loading anything inside the loop. Even if your loop is correct (something I'm not sure about), you'd get the same result.

You would typically want to do something like:

For some values of N

Table1:

NoConcatenate Load * From SomeSource where SomeField = N;

Store * From Table1 Into FileN;

Drop Table Table1;

Loop

Your version doesn't load anything, which means you're always storing the full original table.

Re: Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 13:46:58 GMT

right,

but i need to do a looping to generate qvd for every year/month. but idk how to do this.

Re: Create Multiple QVDs from the YearMonth field

Aurelien_Martinez — Tue, 12 Sep 2023 13:53:38 GMT

Hi,

Grouped_Table:
LOAD
*,
Month(Timestamp(dataFont)) AS Month,
Year(timestamp(dataFont)) AS Year,
(Year(timestamp(dataFont))*100) + (Month(Timestamp(dataFont))*1) as YearMonth
RESIDENT OriginalTable;

FOR Each YearMonth IN FieldValueList('YearMonth')
_tmp:
noconcatenate
Load
  *
resident Grouped_Table
where
  YearMonth=$(YearMonth)
;

STORE _tmp INTO '$(QVD Directory)table_$(YearMonth).qvd';
drop table _tmp;

NEXT YearMonth

Re: Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 14:41:15 GMT

Hi, ty for answer.

This script worked, but only saved a year/month.

there's something can we do?

Re: Create Multiple QVDs from the YearMonth field

Aurelien_Martinez — Tue, 12 Sep 2023 14:49:02 GMT

For example, for june 2023 you want a QVD with all data between january and june? Or just june?

Re: Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 15:54:53 GMT

forget it.

its works. I forgot "next YEARMONTH".

Ty for help me!

Re: Create Multiple QVDs from the YearMonth field

adrianavila — Tue, 12 Sep 2023 17:58:39 GMT

How can I use the date field of the "grouped table" to filter another table and export the qvd of this new table according to the date field as well?

Re: Create Multiple QVDs from the YearMonth field

Aurelien_Martinez — Wed, 13 Sep 2023 08:20:31 GMT

You can use Where Exist https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Exists.htm

like:

another_table:

Load

date

Where exist(dateFont, date)

;

SQL SELECT date From anotherTable;