topic Deleting more that one record with DynamicDataUpdateCommand in QlikView

Deleting more that one record with DynamicDataUpdateCommand

Wed, 06 Jan 2010 11:12:45 GMT

Hello,
I have missed a syntax for DynamicDataUpdateCommand. I would like to update more that one record using DynamicUpdateCommand. I would like to delete 2 records from table DimTime. I tried to use syntax like

DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 3 OR [DimTime.TimeKey] = 4
DELETE FROM [DimTime] WHERE [DimTime.TimeKey] IN (3, 4)

but both is not working.
Is it possible to delete more that one record from table? Because deleting rows one by one takes too much time.

Ramunas

Deleting more that one record with DynamicDataUpdateCommand

Wed, 06 Jan 2010 13:35:01 GMT

Hi Ramunas

We are also identifying duplicate records & marking them separately using Remarks. We are using "PEEK" command .

eg. if(field1=peek(field1),0,1) as duplicates.

Cheers,

Bhushan

Deleting more that one record with DynamicDataUpdateCommand

Thu, 07 Jan 2010 09:31:54 GMT

I have found a partial solution for my problem. Seems I can use several transactions in one DynamicDataUpdateCommand.

Example

Instaed of using

ActiveDocument.DynamicUpdateCommand("DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 3")

ActiveDocument.DynamicUpdateCommand("DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 4")

Possible use

ActiveDocument.DynamicUpdateCommand("DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 3; DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 4")

This option run faster.

Ramunas

Deleting more that one record with DynamicDataUpdateCommand

Thu, 02 Sep 2010 17:17:19 GMT

Thank you so much for following up on your own post for this problem. Using the info from your post, I've written the following two functions for adding and removing values dynamically to a table.

This is JScript from a library I've written for our corporate qlikview stuff. I use it to statistically sample our results:

---CODE----

1 function fisherYates (arr) 2 { if(arr. length == 0) {return arr} else 3 { var i = arr. length, ret = arr. join('®'). split('®') //copy for non-dest ructive 4 ; while ( --i ) 5 { var j; with(Math) j = floor( random() * ( i + 1)) 6 ; var ti = ret[i], tj = ret[j] 7 ; ret[i] = tj 8 ; ret[j] = ti} return ret}} 9 10 function getSample() 11 { with(ActiveDocument) 12 { var size = Variables('vSampleSize'). GetContent(). String 13 ; var field = Variables('vSampleColumn'). GetContent(). String 14 ; DynamicUpdateCommand( 15 'INSERT INTO SampleSet ('+field+',Samp) VALUES (' + 16 fisherYates(Evaluate("concat("+field+",' ')"). split(' ')) 17 . slice(0,size) 18 . join(',Y),(') + ',Y)'). Default}} 19 20 function clearEntries() 21 { with(ActiveDocument) 22 { var field = Variables('vSampleColumn'). GetContent(). String 23 ; DynamicUpdateCommand( 24 'DELETE FROM [SampleSet] WHERE ['+field+'] ='+ 25 Evaluate("concat({<Samp={'Y'}>} "+field+",' ')"). split(' ') 26 . join('; DELETE FROM [SampleSet] WHERE ['+field+'] =')+';') 27 . Default || GetApplication(). MsgBox('Oops!')}} 28 29

---END*----

Alex

Deleting more that one record with DynamicDataUpdateCommand

Thu, 02 Sep 2010 18:34:01 GMT


ramunas wrote:
Hello,
I have missed a syntax for DynamicDataUpdateCommand. I would like to update more that one record using DynamicUpdateCommand. I would like to delete 2 records from table DimTime. I tried to use syntax like
DELETE FROM [DimTime] WHERE [DimTime.TimeKey] = 3 OR [DimTime.TimeKey] = 4
DELETE FROM [DimTime] WHERE [DimTime.TimeKey] IN (3, 4)
but both is not working.
Is it possible to delete more that one record from table? Because deleting rows one by one takes too much time.
Ramunas <div></div>

Now, to actually answer your question: Yes, it is possible, using this JScript function adapted from my code above:

---CODE----

1 /** 2 * Type| dynamicDelete :: Tablestr -> Fieldstr -> QvExpr -> IO Boolean<br/> 3 * Uses a Qv Expression that returns a Space separated list as a 4 * string to generate a dynamic delete command using the values as criteria 5 * @param {Table} table 6 * A string naming a table that exists in this document's data model 7 * @param {Field} field 8 * A string naming a field that exists in this document's data model 9 * @param {QvExpr} fieldcriteria10 * A string containing a valid QlikView expression11 */12 function dynamicDelete(table, field, fieldcriteria)13 { with(ActiveDocument)14 { DynamicUpdateCommand( 15 'DELETE FROM '+table+' WHERE ['+field+'] ='+16 Evaluate(fieldcriteria). split(' ')17 . join('; DELETE FROM '+table+' WHERE ['+field+'] =')+';')18 . Default || GetApplication(). MsgBox('Oops!')}}

---END*----

To use it as I have above, you would call it in the following manner:

----

20 dynamicDelete('SampleSet','CLI_ID',"concat({<Samp={'Y'}>} CLI_ID, ' ')");

---

Or, to solve your specific example, use it like this:

----

22 dynamicDelete('[DimTime]','[DimTime.TimeKey]',"'3 ' & '4'");

---

Hope this helps you!

-Alex