Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to create QV tables in scripts

Hello,

What's the QV script syntax to produce the following tables?  See attached screen.  This is what

I'm looking for.  Given the data in rows 1 thru 5, how do you write the QV code to produce two additional

tables (rows 7-9 and rows 11-15)?

sample_data.jpg

I'm sure this isn't that difficult for some of the more experienced QV developers. 

I appreciate your help.

17 Replies
Highlighted
MVP
MVP

LOAD * INLINE [

REGION , STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

];

LOAD * INLINE [

ROWID, REGION

1 , EAST

2, WEST

];

It will be enough for these two tables to be made and QlikView will associate the two tables since REGION is shared between the two and it will become a "key" field that associates the two tables.

Highlighted
Creator III
Creator III

Sorry, one important requirement I left out in my post.  I need to QV to automatically generate the ROWID in rows 8-9.  It doens't have to be 1 or 2.  It can be 10 or 11.  Once you have these unique values generated and assigned to the

REGION, then using this info to produce the final table which looks like rows 11 - 15.

Thanks

Highlighted
Creator
Creator

If you do not wish to load with inline for such small content then you can modify your xls file source by creating each data set in it's own tabs so you can load them in normal file data way..

Highlighted
Not applicable

Try like below:

InputTable:

LOAD

     REGION ,

     STATE ,

FROM SOURCE

;

TempRegion:

LOAD

     DISTINCT

     RECNO() AS ROWID

     REGION

Resident InputTable

;

Left Join (InputTable)

LOAD

     REGION,

     ROWID

Resident

     TempRegion

;

DROP Table TempRegion;

Highlighted
MVP
MVP

LOAD

    RowNo() AS ROWID,

    *

INLINE [

REGION

EAST

WEST

];

Highlighted
MVP
MVP

If you really just want one single table at the end you can do this:

MAP_REGION:

MAPPING LOAD

    RowNo() AS ROWID,

    REGION

INLINE [

REGION

EAST

WEST

];

LOAD

    ApplyMap('MAP_REGION', REGION) AS ROWID,

    REGION,

    STATE

INLINE [

EAST, NY

EAST, MA

WEST, CA

WEST, WA

]; 


Highlighted
Creator III
Creator III

dathu,

The ROWID's are not generated correctly. 

We should only to expect to see two unique values.  When I ran your scipt, it created 4 values.

result_1.jpg

Highlighted
Creator III
Creator III

Petter,

I got an error when I tried your script:

Field not found - <REGION>

LOAD 

    ApplyMap('MAP_REGION', REGION) AS ROWID, 

    REGION, 

    STATE 

INLINE [ 

EAST, NY 

EAST, MA 

WEST, CA 

WEST, WA 

]

Highlighted
MVP
MVP

Shoud be like this:

LOAD

    ApplyMap('MAP_REGION', REGION) AS ROWID,

    REGION,

    STATE

INLINE [

REGION, STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

] ;