QlikTip #36: Loading a mapping table directly from a QVD file (QV10 SR2+)

In the past it was not possible to load a mapping table directly from a QVD file. We had to use some workarounds to break the optimized load like:

Mapping_Map:
Mapping
LOAD
    Field1,
    Field2
FROM qvdFile.qvd (qvd)
WHERE 1=1;

or

Mappping_Map:
Mapping
LOAD
    Field1,
    Field2,
    1 as Field_temp
FROM qvdFile.qvd (qvd);
DROP FIELD Field_temp FROM Mapping_Map;

or

just loading the fields to a temporary table and then using a RESIDENT LOAD.

Also have a look at the excellent article from Rob Wunderlich on several QVD-topics (“QVD Questions and answers”) where he also mentioned this issue.

Although I have not read any offiicial announcement on that topic a direct load from a QVD file into a mapping load is now possible in QV 10 SR2.

Just a little testing script, if you want to ensure if it really works:

Map:
LOAD * INLINE [
    F1, F2
    A, A-mapped
    B, B-mapped
    C, C-mapped
];
STORE Map INTO Map.qvd(qvd);

DROP TABLE Map;

// The real testing scenario starts here ...
Mapping_Map:
Mapping
Load *
FROM Map.qvd (qvd);

Transactions:
Load
 TransLineID,
 TransID,
 ApplyMap('Mapping_Map', Pick(Ceil(3*Rand1),'A','B','C'),'n/a') as Dim1,
 Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2;
Load
 Rand() as Rand1,
 IterNo() as TransLineID,
 RecNo() as TransID
Autogenerate 1000
 While Rand()<=0.5 or IterNo()=1;




Good News! Although there is now some need to improve our existing scripts to improve the load time by some seconds :)



Bookmark and Share

and tagged , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

3 Comments

  1. Posted May 5, 2011 at 20:49 | Permalink

    Excellent spot! I’ve a few documents to update also!
    - Steve

  2. Posted April 13, 2014 at 12:04 | Permalink

    Nice response in return of this matter with solid arguments and telling the whole thing
    concerning that.

    my webpage – Bungalows – https://www.youtube.com
    -

Post a Comment

(required)
(required)

Your email is never published nor shared.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting