topic Re: Re: Selecting fieldnames in script from variables in QlikView

Selecting fieldnames in script from variables

Mon, 07 Apr 2014 11:49:12 GMT

Hi,

I have to load a table Customers.

I have a DataDictionary sheet in Excel with a list of 125 fieldnames of this table. In field "Relevant" i have selected 25 (n) records with "J"

I Loaded a tabel x and selected the 25 (n) relevant fields, based on the sheet.

Now i want to select only this 25 (n) relevant fields out of table Customers, based on the list of just loaded fields.

I'm thinking to create a variable v_Fieldnames and then fill this variable with the selected relevant fields.

Then i have to LOAD (v_Fieldnames) insteat of LOAD * or manually typing the fieldnames......

Because I have a lot of tables with a lot of (not relevant) fields to load, i would like to automate the selecting of fields...

Can anyone help me please?

Rob.

Re: Selecting fieldnames in script from variables

Mon, 07 Apr 2014 12:09:10 GMT

Something like this

Ex:

Excel:

Load

Relevant

From ExcelFile;

Let vCount = FieldValueCount('Relevant');

For i = 1 to $(vCount)

Let vValue = Peek('Relevant',$(i),Excel);

Customers:

Load

'$(vValue)' as '$(vValue)'

From Customers;

Next;

Re: Selecting fieldnames in script from variables

Mon, 07 Apr 2014 12:54:38 GMT

Hi Ravi, thanks for your quick response.

I tried your solution, but it was not succesfull.

This is an example of the code a tried:

Excel:

LOAD Field //=Fieldname
FROM C:\qlikview\DataDictionary\DataDictionary.xlsx (ooxml, embedded labels, table is Blad1)
WHERE([Tabelnaam QV] = 'Customers' AND Relevant='J');

// This load leads to 1 table with 1 field named "Field" and 25 records with relevant Fieldnames

Let vCount = FieldValueCount('Field');
For i = 1 to $(vCount)
Let vValue = Peek('Field',$(i),Excel);

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Data Source=SQL-03 ...

Customers:
Load '$(vValue)' as '$(vValue)';
SQL SELECT '$(vValue)' as '$(vValue)'
FROM Database.dbo."Customer";

This results in two tables:

1. Excel with 1 field "Field"

2. Customers with 1 field "Name"

The result i wanted was a table Customers with 25 (relevant) fields and 100.000 records.

Regards,

Rob.

Re: Re: Selecting fieldnames in script from variables

Tue, 08 Apr 2014 06:02:32 GMT

Hi,

Can you try like this

1. Create a qvd for customer first

2. use the below script (Script With Qvd);

3. If it is ok, you can try with script ( Script With Sql table)

// Script With Qvd

Excel:

Load Relevant From ExcelFile;

Let vCount = FieldValueCount('Relevant');

For i = 1 to $(vCount);
Let vValue = Peek('Relevant',$(i),Excel);
If $(i)=1 then

Let vRelevant = vValue;
ELSE

Let vRelevant = vTemp&','&vValue;
End IF

IF $(i)=$(vCount) then
Customers:

Load vRelevant From Customers.qvd;
End IF

Let vTemp = vRelevant ;
Next;
Drop Table Excel;

// Script With Sql Table

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Data Source=SQL-03 ...
Excel:
Load Relevant From ExcelFile;
Let vCount = FieldValueCount('Relevant');
For i = 1 to $(vCount);
Let vValue = Peek('Relevant',$(i),Excel);
If $(i)=1 then
Let vRelevant = vValue;
ELSE
Let vRelevant = vTemp&','&vValue;
End IF
IF $(i)=$(vCount) then
Customers:
Load vRelevant
SQL SELECT vRelevant
FROM Database.dbo."Customer";
End IF
Let vTemp = vRelevant ;
Next;
Drop Table Excel;

Note: Since I can't connect to databases to check the exact syntax from sql.But i am sure it will work if you use qvd

Re: Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 08:51:41 GMT

Hi Ravi,

Sorry for my late reaction. I did not forget this issue but, as it did not worked immidiately , i went on developing without this automatic functionallity. Now i have more time to spent on this interesting case, so tried further and further. I did what you asked me to do, so i am trying tot auto selecting fields from a file Customer.qvd.

I think i understand your approach. I made some minor adjustments and with the debug functionality i see what is happening and i understand what is going wrong. When building the vRelevant variable the fieldnames a needed quote is missing. I tried to get this solved but not succesfull.

I would like to sent you an minor example of the customer.qvd and the DataDictionary sheet i created. But i see i can't attach files here. My e-mail adress is: rob_jouvenaar@hotmail.com. This is the latest script:

Excel:

LOAD *

FROM DataDictionary.xlsx (ooxml, embedded labels, table is Blad1)

WHERE([Tabelnaam QV] = 'NAV_Customers' AND Relevant2='J');

Let vCount = FieldValueCount('Relevant');

For i = 1 to $(vCount);

//Let vValue = Peek('Relevant',$(i),Excel); // Field 'Relevant' does not contain fieldname

//Let vValue = Peek('Field',$(i),Excel); // Field 'Field' does contain fieldname: succesfull.

Let vValue = '['&Peek('Field',$(i),Excel)&']'; // Issue : variable gets build without quoting fieldnames....

//Let vValue = '"'&Peek('Field',$(i),Excel)&'"'; // Tried quoting the fieldname here but now to much quotes....

//Let vValue = '['&Peek('Field',$(i),Excel)&']'; // Tried quoting with brackets.. no solution

If $(i)=1 then

Let vRelevant = vValue;

ELSE

Let vRelevant = vTemp&','&vValue;

//Let vRelevant = vTemp&'"'&','&'"'&vValue; // Tried quoting the fieldname but problem with the "last" quote

End IF

IF $(i)=$(vCount) then

Customers:

//Load vRelevant From Customers.qvd;

Load $(vRelevant) From Customers.qvd;

End IF

Let vTemp = vRelevant ;

Next;

//Drop Table Excel;

Re: Re: Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 10:38:08 GMT

Hi Rob,

Please check the attachment.

Hope it will solve your issue.

Regards

RaviKumar

Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 10:44:34 GMT

Ravi,

Thanks for your reaction.

I use a QlikView Personal Edition. Can you please sent me the qvs instead?

Re: Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 10:54:23 GMT

Here is the script

//Customer:
//Load * Inline [
//First One, Second One, Third One, Fourth One, Fifth One
//1, 2, 3, 4, 5
//6, 7, 8, 9, 10
//];
//Store Customer into Customers.qvd;

// Exit Script

Excel:
Load * Inline [
Relevant
First One
Second One
Third One
];
//Fourth One
//Fifth One

Let vCount = FieldValueCount('Relevant');
For i = 0 to $(vCount)-1;
Let vValue = Peek('Relevant',$(i),Excel);

If $(i)=0 then
Let vRelevant = vValue;
ELSE
Let vRelevant = vTemp&','&vValue;
End IF
IF $(i)=$(vCount)-1 then
Let vTest =Replace('['&vRelevant&']',',','],[');
Customers:
Load
$(vTest)
FROM
Customers.qvd
(qvd);
End IF
Let vTemp = vRelevant;
Next;

Drop Table Excel;

Note: First Execute the commented part and comment again.

If this works then do modifications as per your data.

Re: Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 12:08:03 GMT

Hi Ravi,

Your example worked! I did the modifications on my data and that also worked!!
Fine and thanks a lot!.

I do not understand what you did here: Let vTest =Replace('['&vRelevant&']',',','],[');

Can you explain this a little to me?

Rob.

Re: Selecting fieldnames in script from variables

Mon, 28 Apr 2014 12:21:43 GMT

Hi Rob,

First, good to here the script works for you.

Comming to your question,

Take the variable vRelavent in a text object,It will show the values like --> First One,Second One,Third One

But we required like [First One],[Second One],[Third One]

That is why, i have created one more variable and storing the converted result like this

Let vTest =Replace('['&vRelevant&']',',','],[');

And finally using this variable in the load.

Hope i answerd to your question.

Please mark it correct and close the thread for reference.

Regards

Ravikumar