topic Re: How to create QV tables in scripts in QlikView

How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 18:24:04 GMT

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)?

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

I appreciate your help.

Re: How to create QV tables in scripts

petter — Fri, 12 Feb 2016 18:28:11 GMT

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.

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 18:33:44 GMT

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

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 18:34:19 GMT

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

Re: How to create QV tables in scripts

Fri, 12 Feb 2016 18:34:35 GMT

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;

Re: How to create QV tables in scripts

petter — Fri, 12 Feb 2016 18:37:04 GMT

LOAD
    RowNo() AS ROWID,
    *
INLINE [
REGION
EAST
WEST
];

Re: How to create QV tables in scripts

petter — Fri, 12 Feb 2016 18:42:17 GMT

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
];

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 18:50:17 GMT

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.

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 18:52:34 GMT

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

]

Re: How to create QV tables in scripts

petter — Fri, 12 Feb 2016 19:07:13 GMT

Shoud be like this:

LOAD

ApplyMap('MAP_REGION', REGION) AS ROWID,

REGION,

STATE

INLINE [

REGION, STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

] ;

Re: How to create QV tables in scripts

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 19:36:41 GMT

The ROWID is not being displayed when I ran it.

Re: How to create QV tables in scripts

maxgro — Fri, 12 Feb 2016 19:53:07 GMT

you can use autonumber to make unique ROWID for each REGION

SOURCE:

LOAD * INLINE [

REGION , STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

];

OUTPUT1:

load AutoNumber(REGION) as ROWID, REGION Resident SOURCE;

Left Join (SOURCE) load * Resident OUTPUT1;

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 20:06:37 GMT

I see you've a screenshot showing the output I wanted. When I looked the model, I see this and none of the table

gives me the ROWID, REGION and STATE columns. Is this what I'm expecting to see?

Re: How to create QV tables in scripts

maxgro — Fri, 12 Feb 2016 20:16:05 GMT

do you really need 3 tables? why?

or may be you can stay with 1?

SOURCE:

LOAD * INLINE [

REGION , STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

];

Left Join (SOURCE) load Distinct REGION, AutoNumber(REGION) as ROWID Resident SOURCE;

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 20:19:03 GMT

MG,

This is perfect ! That's exactly what I was looking for.

Thanks.

Re: How to create QV tables in scripts

maxgro — Fri, 12 Feb 2016 20:27:58 GMT

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: How to create QV tables in scripts

Anonymous — Fri, 12 Feb 2016 20:38:45 GMT

MG,

I'm just coming back to what you had posted earlier which is this

SOURCE:

LOAD * INLINE [

REGION , STATE

EAST, NY

EAST, MA

WEST, CA

WEST, WA

];

OUTPUT1:

load AutoNumber(REGION) as ROWID,

REGION

Resident SOURCE;

Left Join (SOURCE) load * Resident OUTPUT1;

What would I need to do so I can see the last resultset in the data diagram? I guess it would be the result of running that very last statement in above which the LEFT JOIN. I tried to alias it with a table name right before it and I still didn't see it in the diagram. I know you already provided a perfect solution to me but I wanted to learn how to get this

working as well.

Re: How to create QV tables in scripts

maxgro — Fri, 12 Feb 2016 21:00:35 GMT

Not sure to understand your last question

In the table viewer (ctrl + t) you see the qlik data model.

You don't need a table for every different chart you want to show.

You don't need all the fields of your chart in the same table.

You can build a chart using all the fields in the data model.

If you want this, just add a tablebox object with the 3 fields