Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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..
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;
LOAD
RowNo() AS ROWID,
*
INLINE [
REGION
EAST
WEST
];
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
];
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.
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
]
Shoud be like this:
LOAD
ApplyMap('MAP_REGION', REGION) AS ROWID,
REGION,
STATE
INLINE [
REGION, STATE
EAST, NY
EAST, MA
WEST, CA
WEST, WA
] ;