Qlikview - create tables based on unique field values

[Patreson31]

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!

[rubenmarin]

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

[Patreson31]

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

[rubenmarin · Accepted solution]

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.

[Patreson31]

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!

[rubenmarin]

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

[Patreson31]

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