Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER 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
Henric_Cronström

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
Henric_Cronström

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

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