CODEWITHSUNDEEP
splitconcatenationqlikview
SPP
SPP

Reputation: 21

Split one qlikview (qvd) file into two qvd file

I have qvd file that contain data for two year. I have to split that data into two qvd files, one belonging to the current year and the one belonging to next year or last year. I have tried using time analysis. But now I'm lost which code to use. Any idea or help please? Thanks in advance.

Screenshot of the variable field:

Date Data screenshot

YearsToLoad:
Load
    left(BATCH_NO, 4) as BATCH_NO
;
Load
    distinct BATCH_NO
From 
    Current.qvd (qvd)
;   

for i = 1 to FieldValueCount('BATCH_NO')
    let vYear = FieldValue('BATCH_NO', $(i));

    Years:
    Load
        $(vYear)    as BATCH_NO
    AutoGenerate(1)
    ;

    Current:
    Load
        BATCH_NO
    From
        Current.qvd (qvd)   
    Where
        left(BATCH_NO, 4) = $(vYear)
    ;

    Store  Current into RawDataLoad_$(vYear).qvd;
    Drop Tables Years, Current;

next

    Drop Table YearsToLoad;

When I hard code the number, my script is like this:

[Last]:
NoConcatenate
load *
Resident CurrentData
where BATCH_NO >= 20150101 AND BATCH_NO < 20160101 ;

[Current]:
NoConcatenate
load *
Resident CurrentData
where BATCH_NO >= 20160101 AND BATCH_NO < 20170101 ;

STORE Current into Current.qvd(qvd);
STORE Last into Last.qvd(qvd);

Then this is the qvd file I get for "Last.qvd"

Upvotes: 1

Views: 1028

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

The script below will load the distinct Years from the main qvd, loop through the values and generate qvd for each year. The first(commented) part is the sample data I'm using for this example. 

//RawData:
//Load * Inline [
//  Value, Year
//  1    , 2013
//  2    , 2013
//  3    , 2014
//  4    , 2014
//  5    , 2014
//  6    , 2015
//  7    , 2015
//  8    , 2015
//  9    , 2016
//  10   , 2016
//];
//
//Store RawData into RawData.qvd;

// Load the distinct Year values from the main qvd
YearsToLoad:
Load
    distinct Year as YearToLoad
From 
    RawData.qvd (qvd)
;   

// Start looping through the distinct values
for i = 1 to FieldValueCount('YearToLoad')
    let vYear = FieldValue('YearToLoad', $(i));

    // Load the current iteration value
    // The resulted field name must match the field name from the next 
    // load. This way will keep the qvd optimized load
    Years:
    Load
        $(vYear)    as Year
    AutoGenerate(1)
    ;

    RawDataLoad:
    Load
        Value,
        Year
    From
        RawData.qvd (qvd)   
    Where
        Exists(Year) // this is the important bit that will filter the qvd
    ;

    // Store the filtered qvd
    Store RawDataLoad into RawDataLoad_$(vYear).qvd;
    // Drop the temp tables
    Drop Tables Years, RawDataLoad;

next

// Drop the table that loaded the distinct Year values
Drop Table YearsToLoad;

Update

When there is no dedicated field that contains the year value and there is only date field (in this case the format is YYYYMMDD) you can use the following script. This will be slower since the qvd optimized load will be broken.

//RawData:
//Load * Inline [
//  Value, Date
//  1    , 20130102
//  2    , 20130103
//  3    , 20140101
//  4    , 20140102
//  5    , 20140103
//  6    , 20150101
//  7    , 20150102
//  8    , 20150103
//  9    , 20160104
//  10   , 20160105
//];
//
//Store RawData into RawData.qvd;
//
//exit Script;

YearsToLoad:
Load
    left(Date, 4) as YearToLoad
;
Load
    distinct Date
From 
    RawData.qvd (qvd)
;   

for i = 1 to FieldValueCount('YearToLoad')
    let vYear = FieldValue('YearToLoad', $(i));

    RawDataLoad:
    Load
        Value,
        Date
    From
        RawData.qvd (qvd)   
    Where
        left(Date, 4) = $(vYear)
    ;

    Store RawDataLoad into RawDataLoad_$(vYear).qvd;
    Drop Table RawDataLoad;

next

Drop Table YearsToLoad;

Upvotes: 1

Related Questions