topic MAcro problem.. in QlikView

MAcro problem..

Thu, 20 Aug 2009 21:39:31 GMT

Hi Guys,

A question related to Macro.. I have a vStichtag as a variable and for example is assigned as a date '31.12.2008'

By selecting this date I select all the values which are '31.12.2008'<=Table.BEGDA and '31.12.2008' >=Table.ENDDA

I resignate the Macro selectstichtag1 to Vstichtag in the Properties of the documents-> macros->Variable

I set a variable l_tabellen_stichtag1_gesteuert in skript to all the tables which I want to loop in the macro according to the variable '31.12.2008'

ActiveDocument.Fields(fieldname).Unlock at this position I am getting a error..

can u pls help and tell whether the code is correct?

sub SelectStichtag
set v = ActiveDocument.Variables("vStichtag")
x = "<=" & v.GetContent.String
SelectAndLock "Table1.BEGDA",x
SelectAndLock "Table2.BEGDA",x
SelectAndLock "Table3.BEGDA",x
SelectAndLock "Table4.BEGDA",x
SelectAndLock "Table5.BEGDA",x
SelectAndLock "Table6.BEGDA",x
SelectAndLock "Table7.BEGDA",x
SelectAndLock "Table8.BEGDA",x
SelectAndLock "Table9.BEGDA",x
SelectAndLock "Table10.BEGDA",x
SelectAndLock "Table11.BEGDA",x
SelectAndLock "Table12.BEGDA",x

x = ">=" & v.GetContent.String
SelectAndLock "Table1.ENDDA",x
SelectAndLock "Table2.ENDDA",x
SelectAndLock "Table3.ENDDA",x
SelectAndLock "Table4.ENDDA",x
SelectAndLock "Table5.ENDDA",x
SelectAndLock "Table6.ENDDA",x
SelectAndLock "Table7.ENDDA",x
SelectAndLock "Table8.ENDDA",x
SelectAndLock "Table9.ENDDA",x
SelectAndLock "Table10.ENDDA",x
SelectAndLock "Table11.ENDDA",x
SelectAndLock "Table12.ENDDA",x

end sub

sub selectStichtag1

dim i_tabellenliste, a_tabelle, i_tabelle, i_feld
dim i_selektion_begda, i_selektion_endda, i_stichtag

i_stichtag = ActiveDocument.Variables("vStichtag")_
.getContent.String

i_tabellenliste = ActiveDocument.Variables("l_tabellen_stichtag1_gesteuert")_
.getContent.String

a_tabelle = split(i_tabellenliste,",")

i_selektion_begda = "<=" & i_stichtag
i_selektion_endda = ">=" & i_stichtag

for each i_tabelle in a_tabelle
i_tabelle = trim(i_tabelle)
i_feld = i_tabelle & ".BEGDA"
call SelectAndLock (i_feld,i_selektion_begda)
i_feld = i_tabelle & ".ENDDA"
call SelectAndLock (i_feld,i_selektion_endda)

next

end sub

sub SelectAndLock (fieldname, selection)
ActiveDocument.Fields(fieldname).Unlock
ActiveDocument.Fields(fieldname).Clear
ActiveDocument.Fields(fieldname).Select selection,true,1
ActiveDocument.Fields(fieldname).Lock
end sub

Thanks in advance

sravan

MAcro problem..

Thu, 20 Aug 2009 21:50:54 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0474.Dok1.doc:550:0]

MAcro problem..

disqr_rm — Thu, 20 Aug 2009 23:00:45 GMT

Can you post a sample qvw here?

MAcro problem..

Fri, 21 Aug 2009 14:02:30 GMT

Hi Rakesh.. sensible data .. I cannot..

AW:Re: MAcro problem..

biester — Sat, 22 Aug 2009 00:31:12 GMT

Hi,

at first sight I would say: the function SelectAndLock must get an invalid parameter "fieldname"; I could imagine that one time i_tabelle is empty and so parameter is ".BEGDA" - so this invalid field cannot be unlocked (hence "Objekt erforderlich ..."). That would be my first suspicion. Perhaps you should debug the i_tabelle or the fieldname-parameter with MsgBox to check.

Rgds,
Joachim

AW:Re: MAcro problem..

Mon, 24 Aug 2009 16:50:14 GMT

@ Biester

Thanks for the explanation.. I think u r explanation makes sense. How can I change the code to get the correct data with out getting this "Object erforderlich..." and I did not debug in Macro untill now.. I need to see how to do it if any explanation is there in handbook..

Thanks

AW:Re: AW:Re: MAcro problem..

biester — Mon, 24 Aug 2009 17:05:28 GMT

Hi Sravan,

I'd suggest to simply check the parameters and slightly modify your sub as follows (hope I made no mistake here):

sub SelectAndLock (fieldname, selection)
'this is the line to check what parameters this sub really receives
MsgBox "Field: " & fieldname & " - selection: " & selection
ActiveDocument.Fields(fieldname).Unlock
ActiveDocument.Fields(fieldname).Clear
ActiveDocument.Fields(fieldname).Select selection,true,1
ActiveDocument.Fields(fieldname).Lock
end sub

Let's try what the MsgBox tells us ... Hope this helps,

Rgds,
Joachim

AW:Re: AW:Re: MAcro problem..

Mon, 24 Aug 2009 18:16:56 GMT

@Biester

That was great. Ya the macro selects the list of tables and this msgBox works.. After all the tables are finished it lands again at the line(Below given) with the Problem "Object erforderlich...". That means what you said was correct. The Invalid field cannot be unlocked when the I_tabelle is empty. What can be done here??


ActiveDocument.Fields(fieldname).Unlock

Thanks for the response.. That helped me a lot..

AW:Re: AW:Re: AW:Re: MAcro problem..

biester — Mon, 24 Aug 2009 18:33:25 GMT

Hi Sravan,

well, if I analyze the thing right, the situation might be thus:

You have variable l_tabellen_stichtag1_gesteuert, where you have a list of the needed tables, separated by comma.
You read this QV-Variable into the VBScript variable i_tabellenliste and then split it. If i_tabelle is empty (I assume it's the last one), then the variable ends with a comma, meaning it must look like:
"Tabelle1,Tabelle2,Tabelle3,"
So the split(i_tabellenliste,",") delivers an array with four elements: Tabelle1,Tabelle2,Tabelle3 AND NULL (because after the last comma there is NOTHING).

So I'd suggest you check this. Perhaps as so often the solution lies in a comma too much ;-).

Rgds,
Joachim

AW:Re: AW:Re: AW:Re: MAcro problem..

Mon, 24 Aug 2009 19:33:41 GMT

Hi Joachim,

It seems reasonable what you said and the explanation with

I_tabellen_stichtag1_gesteuert is correct. But the problem is not with a comma. everything seems to be correct in the I_tabellen_stichtag1_gesteuert and still the same problem 😞

By the way very good interpretation 🙂

AW:Re: AW:Re: AW:Re: AW:Re: MAcro problem..

biester — Mon, 24 Aug 2009 19:50:21 GMT

Hi Sravan,

can you hear my brains working ? I'm almost getting a "Hirnkrampf" imagining what else it could be 😉

It would be very helpful to know the exact value of the ominous variable I_tabellen_stichtag1_gesteuert, perhaps I can see there what I can't think of now. Could you perhaps post it, I think it won't contain to much sensible data, does it?

Rgds,
Joachim