Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
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.
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
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;
Hi Rob,
Please check the attachment.
Hope it will solve your issue.
Regards
RaviKumar
Ravi,
Thanks for your reaction.
I use a QlikView Personal Edition. Can you please sent me the qvs instead?
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.
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.
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