Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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..

Not applicable
Author

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;

petter
Partner - Champion III
Partner - Champion III

LOAD

    RowNo() AS ROWID,

    *

INLINE [

REGION

EAST

WEST

];

petter
Partner - Champion III
Partner - Champion III

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

]; 


Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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 

]

petter
Partner - Champion III
Partner - Champion III

Shoud be like this:

LOAD

    ApplyMap('MAP_REGION', REGION) AS ROWID,

    REGION,

    STATE

INLINE [

REGION, STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

] ;