topic Qlikview - create tables based on unique field values in QlikView

Qlikview - create tables based on unique field values

Patreson31 — Sat, 16 Nov 2024 00:49:57 GMT

Hi there,

I've been trawling the forums but not managed to work this one out, any help would be awesome thanks,

I've got a blob of raw data coming in from unique sensors col (A) at random time frequency col (B) that have a value reading col (C). I need to convert this data in script to produces individual tables for each sensor that includes; their unique sensor, individual timestamp and related value - this will let me work with them downstream in the data model.

Again, thanks for any help!

Re: Qlikview - create tables based on unique field values

rubenmarin — Thu, 16 Apr 2020 11:33:16 GMT

Hi, You can create a table and a qvd for each sensor using a bucle that reads the table for each value in sensors field, like:

tmpSensor: // LOAD Data ; FOR vValueNum=1 to FieldValueCount(Sensor) LET vSensor = FieldValue('Sensor', $(vValueNum)); $(vSensor): NoConcatenate LOAD * From Resident tmpSensor Where Sensor='$(vSensor)'; STORE $(vSensor) into [Path\$(vSensor).qvd] (qvd); DROP Table $(vSensor); NEXT DROP Table tmpSensor;

Then you can read from qvds which stores data of each Sensor.

If there is a lot of data loading time can be improved creating a qvd from initial data and doing an optimized load of that qvd using Exists().

Re: Qlikview - create tables based on unique field values

Patreson31 — Thu, 16 Apr 2020 13:23:21 GMT

Hi Rubenmarin,

Thanks for the response, I like the approach - individual qvd's would really work as more data comes in.

Sorry if I'm being a bit thick, I'm having a couple of problems with the script which I'm trying to work through but if you have any suggestions:

"""Semantic error

FOR vValueNum= 1 to FieldValueCount(Sensor)"""

"""Syntax error

Unexpected token: ')', expected one of: '(', 'ZTestw_z', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'not', 'bitnot', 'LITERAL_NUMBER', ...

LET vSensor = FieldValue('Sensor', >>>>>>)<<<<<<"""

"""Unknown statement: :
NoConcatenate LOAD * From tmpSensor Where Sensor=''

:
NoConcatenate LOAD * From tmpSensor Where Sensor="""

"""Syntax error

Unexpected token: '[Path\.qvd]', expected one of: ',', 'as', 'from', 'into'

STORE into >>>>>>[Path\.qvd]<<<<<< (qvd)"""

"""Semantic error

The control statement is not correctly matched with its corresponding start statement

NEXT"""

Thanks again

Re: Qlikview - create tables based on unique field values

rubenmarin — Thu, 16 Apr 2020 13:31:26 GMT

Ok, I didn't tested so it had a few syntax errors, this one doesn't gives any errors:

tmpSensor: LOAD * Inline [ Sensor, Value A, 3 B, 5 ]; FOR vValueNum=1 to FieldValueCount('Sensor') LET vSensor = FieldValue('Sensor', $(vValueNum)); [$(vSensor)]: NoConcatenate LOAD * Resident tmpSensor Where Sensor='$(vSensor)'; STORE $(vSensor) into [.\$(vSensor).qvd] (qvd); DROP Table $(vSensor); NEXT DROP Table tmpSensor;

You'll need to adapt tmpSensor to your original data and maybe change the .\ path in store to the one you want to store qvds.

Re: Qlikview - create tables based on unique field values

Patreson31 — Thu, 16 Apr 2020 15:37:08 GMT

That's fantastic Rubenmarin. Sorry, there is one final niggle where I get an error:

"""Syntax error

Unexpected token: '*', expected one of: ':', 'IDENTIFIER', 'LITERAL_FIELD', 'LITERAL_STRING', 'IDENTIFIER', 'LITERAL_STRING', 'LITERAL_FIELD', ...

STORE >>>>>>*<<<<<< End Repeat..."""

after the loop has stored the first qvd in the directory. Gone down the rabbit hole reading about BNF settings but wonder if there is perhaps a simpler solution.

Thanks again!

Re: Qlikview - create tables based on unique field values

rubenmarin — Thu, 16 Apr 2020 15:44:12 GMT

Maybe is beacuse the table has some starnge character, try enclosing all table names between []:

STORE [$(vSensor)] into [.\$(vSensor).qvd] (qvd); DROP Table [$(vSensor)];

If the name of the sensor is just * it can't be saved as that name, please check that teher is no Sensor with that name.

Also you can use purgechar to avoid forbidden characters for file names:

LET vSensor = FieldValue('Sensor', $(vValueNum)); LET vSensorTableName = PurgeChar(FieldValue('Sensor', $(vValueNum)), '*/\:<>?="';

And use vSensorTableName instead of vSensor, just keep vSensor on the Where clause

Re: Qlikview - create tables based on unique field values

Patreson31 — Thu, 16 Apr 2020 16:31:15 GMT

Phenomenal - purgechar was just the trick. Thanks very much again!