Partition dataset into multiple QVDs by date

[CasperQlik]

Hey Qlik Community

I have one big QVD file that contains all my data sorted by date.

I need to split the dataset into smaller QVDs by date and season. 
Each row has a date and either marked summer or winter. 
I need to create QVDs like this:

data_2010_summer.qvd

data_2010_winter.qvd

data_2011_summer.qvd

data_2011_winter.qvd

data_2012_summer.qvd

data_2012_winter.qvd

...

data_current_year_summer.qvd

data_current_year_winter.qvd

Any help is appreciated!

//Casper

[LRuCelver · Accepted solution]

Add fields for the year and season to the large table. Extract all combinations of year and season to a new table. Loop over that table and create a new, temporary table from the large table where you filter for the year and season you are currently looping over. Store that temporary table as a QVD and then drop it. Once you are done, drop the other tables you no longer need.

Here is a bacis mockup for a script that does what I described:

Map_Season:
Mapping Load * Inline [
	Month, Season
    1,  winter
    2,  winter
    3,  winter
    4,  summer
    5,  summer
    6,  summer
    7,  summer
    8,  summer
    9,  summer
    10, winter
    11, winter
    12, winter
];

Data:
NoConcatenate Load
	...,
    Year,
    ApplyMap('Map_Season', Month, 'MISSING') as Season
From [.../largedataset.qvd] (qvd);

Years_Seasons:
NoConcatenate Load Distinct
	Year,
    Season
Resident Data;

For vIdx = 0 to NoOfRows('Years_Seasons') - 1
	Let vYear = Peek('Year', $(vIdx), 'Years_Seasons');
	Let vSeason = Peek('Year', $(vSeason), 'Years_Seasons');
    
    ToBeStored:
    NoConcatenate Load
    	...
    Resident Data
    Where Year = $(vYear) and Season = '$(vSeason)';
    
    Store ToBeStored into '.../data_$(vYear)_$(vSeason).qvd' (qvd);
    Drop Table ToBeStored;

Next vIdx
Let vIdx;
Let vYear;
Let vSeason;

Drop Tables Years_Seasons, Data;

I used a map to assign the seasons. You could also use ifs, pick, etc.

[CasperQlik]

Hi, and thank you for your response.

I can almost get it to work.

I encounter an issue when I include the Season variable and get the following error:

If I remove the Season variable I can get the script to run and function as intended.

Do you have a suggestion to what I can do here?

//Casper

[anat]

Seems something wrong with variable Vseason. Check is any value in vseason

[marcus_sommer]

vSeason is not the loop-counter else it's vIdx - both peek() calls needs to be looped with vIdx.

[CasperQlik]

I stumbled on that too.

I changed the line from this: Let vSeason = Peek('Year', $(vSeason), 'Years_Seasons');

to this: Let vSeason = Peek('Season', $(vIdx), 'Years_Seasons');

I changed the loop counter and the argument to the Peek function. Otherwise it would be assigned the value for year and not season.

But I get this error now:

I don't get it since 2005 has both winter and summer as possible values in the table.

I am not up to date on loops in Qlik - but I can't tell if I made a mistake on my end or if there is something wrong with the logic. My intuition tells me that I need a second inner loop to create the two QVDs for each season.

//Casper

[steeefan]

The error message says "Field 'winter' not found", meaning the script is comparing the field Season to a non-existent field winter, not the string "winter". It seems that you have a slight mistake in your code:

Probably your code:
WHERE Year = $(vYear) AND Season = $(vSeason)

Should be:
WHERE Year = $(vYear) AND Season = '$(vSeason)'

[CasperQlik]

Thank you so much! This was the crucial part I overlooked. The original solution did have the variable in quotes and I changed it without noticing. I marked the original answer as the solution.