Create Multiple QVDs from the YearMonth field

[adrianavila]

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?

[Or]

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.

[adrianavila]

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?

[Or]

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.

[adrianavila]

right, 

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

[Aurelien_Martinez · Accepted solution]

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

[adrianavila]

Hi, ty for answer.

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

there's something can we do?

[Aurelien_Martinez]

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

[adrianavila]

forget it.

its works. I forgot "next YEARMONTH".

xD

Ty for help me!

[adrianavila]

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?