Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

SQL: Concatenate, join and create linktable

Hi

I hope someone can help me. I got eight different accessfiles with data. I would like to create a linktable with all the data (date, age, sex, questions regarding information, quality and so on) except ID since they are numbered 1, 2, 3... and so on in all the eight files.

I got some other data in the same qlikviewfile so when I have tried concatenate the information from these eight files have been added to a table with data from an excelfile...

I have attached the code I have at the moment and a picture.

Thanks in advance.

/A

1 Solution

Accepted Solutions
Employee
Employee

Re: SQL: Concatenate, join and create linktable

It seems to me that all eight tables contain the same type of data, but for different location. If so, you should concatenate them.

You could probably do it in a For - Next loop:

Set vConcatenate = ;

For each vSource in 'Gopshus', 'Prästholmen', 'Simhall', 'Lomsmyren', 'Hemus', 'Sanda', 'Friluftsbad', 'Siljansleden'

          OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\TEKNISKA FÖRVALTNINGEN\Tekniska vht 2010\Qlikview\$(vSource).accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDBSmiley Frustratedystem database="";Jet OLEDB:Registry Path="";Jet OLEDBSmiley Very Happyatabase Password="";Jet OLEDB:Engine Type=6;Jet OLEDBSmiley Very Happyatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBSmiley Very Happyon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDBSmiley FrustratedFP=False;Jet OLEDBSmiley Frustratedupport Complex Data=False];

          $(vConcatenate)

          Load *,

                    '$(vSource)' as Source;

          SQL SELECT * FROM `Undersökning`;

          Set vConcatenate = Concatenate;

Next vSource

Note that I use the variable in the connect string.

Note also that there is an additiona field 'Source' created, so that you can see where the data comes from.

Good Luck!

HIC

3 Replies
Employee
Employee

Re: SQL: Concatenate, join and create linktable

It seems to me that all eight tables contain the same type of data, but for different location. If so, you should concatenate them.

You could probably do it in a For - Next loop:

Set vConcatenate = ;

For each vSource in 'Gopshus', 'Prästholmen', 'Simhall', 'Lomsmyren', 'Hemus', 'Sanda', 'Friluftsbad', 'Siljansleden'

          OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\TEKNISKA FÖRVALTNINGEN\Tekniska vht 2010\Qlikview\$(vSource).accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDBSmiley Frustratedystem database="";Jet OLEDB:Registry Path="";Jet OLEDBSmiley Very Happyatabase Password="";Jet OLEDB:Engine Type=6;Jet OLEDBSmiley Very Happyatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBSmiley Very Happyon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDBSmiley FrustratedFP=False;Jet OLEDBSmiley Frustratedupport Complex Data=False];

          $(vConcatenate)

          Load *,

                    '$(vSource)' as Source;

          SQL SELECT * FROM `Undersökning`;

          Set vConcatenate = Concatenate;

Next vSource

Note that I use the variable in the connect string.

Note also that there is an additiona field 'Source' created, so that you can see where the data comes from.

Good Luck!

HIC

Not applicable

Re: SQL: Concatenate, join and create linktable

Perfekt, thank you.

Does anyone know if I can make an average of an average?

I got this expression:

num(Avg({$<Source={'$Gopshus'}, Kvalité_Fråga_1={">0"} >} Kvalité_Fråga_1) +

Avg({$<Source={'$Gopshus'}, Kvalité_Fråga_2={">0"} >} Kvalité_Fråga_2) +

Avg({$<Source={'$Gopshus'}, Kvalité_Fråga_3={">0"} >} Kvalité_Fråga_3) +

Avg({$<Source={'$Gopshus'}, Kvalité_Fråga_4={">0"} >} Kvalité_Fråga_4))/4

But for another source there are more questions regarding "Kvalité" so instead of dividing every expression with the number of questions, can I make an expression where I can ad question 1...question X and the average of those?

Thanks in advance.

/A

lorenwood
New Contributor III

Re: SQL: Concatenate, join and create linktable

Thanks this helped me with a similar problem

Community Browser