Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
hic
Former Employee
Former Employee

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 OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support 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

View solution in original post

3 Replies
hic
Former Employee
Former Employee

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 OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support 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
Author

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
Contributor III
Contributor III

Thanks this helped me with a similar problem