Re: Joining Multiple Data Connections - Qlik Community

hammermill21 · ‎2018-06-04

Hello everyone,

I currently have an app that has multiple data connections for different sites. They all have the same type of data but needs to be separated by the site. What would be the best way of combing these connections?

Currently is it set that each load of data has the site name in front of it, like this:

[root]:

LOAD TEXT ([Barcode]) AS [Tower Barcode],

[Username] AS [Tower Username],

[Inspection Date] AS [Get Date],

[Inspection Time] AS [Tower Inspection Time],

[Item Type] AS [Tower Item Type],

[Building] AS [Tower Building],

[Floor] AS [Tower Floor],

[Location] AS [Tower Location],

[Room Number] AS [Tower Room Number],

[Description] AS [Tower Description],

[Comments] AS [Tower Comments],

[Pass] AS [Tower Pass],

[Failure Mode] AS [Tower Failure Mode],

So Tower was added to each name, but there is a point where this data needs to be combined by Item type for example.

I have attached a sample of my data. Thank you.

petter · ‎2018-06-04

I would recommend that you keep all the rows from all the sources in a single table. To distinguish which source that provided the rows you need a field containing the name of the source. I could be named simply [Source].

Since the various sources doesn't have exactly the same fields although a number of them are common you will have to tell Qlik to concatenate the rows into one table anyway by using the prefix CONCATENATE for the LOAD statements.

So the load script would look like this for the first two tables:

LIB CONNECT TO 'ATG Tower (uhealth_54171224)';

RestConnectorMasterTable:

SQL SELECT

"Barcode",

"Username",

"Inspection Date",

"Inspection Time",

"Item Type",

"Building",

"Floor",

"Location",

"Room Number",

"Description",

"Comments",

"Pass",

"Failure Mode",

"DateAdded",

"DateRetired",

"ReasonRetired",

"Make",

"Manufacturer",

"ModelNo",

"OccupancyType",

"InspectionFrequency",

"New Barcode"

FROM JSON (wrap on) "root";

[EQUIPMENT]:

LOAD

'Tower' AS Source,

TEXT([Barcode]) AS [Barcode],

[Username] AS [Username],

[Inspection Date] AS [Get Date],

[Inspection Time] AS [Inspection Time],

[Item Type] AS [Item Type],

[Building] AS [Building],

[Floor] AS [Floor],

[Location] AS [Location],

[Room Number] AS [Room Number],

[Description] AS [Description],

[Comments] AS [Comments],

[Pass] AS [Pass],

[Failure Mode] AS [Failure Mode],

[DateAdded] AS [DateAdded],

[DateRetired] AS [DateRetired],

[ReasonRetired] AS [ReasonRetired],

[Make] AS [Make],

[Manufacturer] AS [Manufacturer],

[ModelNo] AS [ModelNo],

[OccupancyType] AS [OccupancyType],

[InspectionFrequency] AS [InspectionFrequency],

[New Barcode] AS [New Barcode]

RESIDENT

RestConnectorMasterTable

WHERE

NOT Wildmatch([Barcode], '*_R');

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ATG Tower Inventory (uhealth_54171224)';

RestConnectorMasterTable:

SQL SELECT

"Barcode",

"Item Type",

"Building",

"Floor",

"Location",

"Room Number",

"Description",

"DateAdded",

"Make",

"Manufacturer",

"ModelNo",

"OccupancyType",

"New Barcode",

"UserDef6"

FROM JSON (wrap on) "root";

CONCATENATE (EQUIPMENT) LOAD

'ATG Tower Inventory' AS Source,

TEXT ([Barcode]) AS [Barcode],

[Item Type] AS [Item Type],

[Building] AS [Building],

[Floor] AS [Floor],

[Location] AS [Location],

[Room Number] AS [Room Number],

//[DateAdded] AS [DateAdded],

[Make] AS [Make],

[Manufacturer] AS [Manufacturer],

[ModelNo] AS [ModelNo],

[OccupancyType] AS [OccupancyType],

[New Barcode] AS [New Barcode]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

// Keep doing the same as with the ATG Tower Inventory for the remaing tables in the rest of the load script

hammermill21 · ‎2018-06-05

Hi Petter,

Thank you so much for your help. I made the changes as per your post but now when it gets to the 4th site I get an error: But the Table Lennar is there, so I'm not sure what is happening. If I remove that tab/script it runs just fine but has a bunch of synthetic keys.

The following error occurred:

Table 'Lennar' not found

The error occurred here:

CONCATENATE (Lennar) LOAD 'ATG Lennar Inventory' AS Source, TEXT ([Barcode]) AS [Barcode], [Item Type] AS [Item Type], [Building] AS [Building], [Floor] AS [Floor], [Location] AS [Location], [Room Number] AS [Room Number], [Description] AS [Description], [DateAdded] AS [DateAdded], [Make] AS [Make], [Manufacturer] AS [Manufacturer], [ModelNo] AS [ModelNo], [OccupancyType] AS [OccupancyType], [New Barcode] AS [New Barcode] RESIDENT RestConnectorMasterTable

petter · ‎2018-06-06

You have to stick to using CONCATENATE (EQUIPMENT) so you get all the rows in one single table. Don't change the reference to EQUIPMENT for each new CONCATENATE.

hammermill21 · ‎2018-06-06

Ok that works but then I get a bunch of synthetic keys:

Lines fetched: 767 EQUIPMENT << RestConnectorMasterTable Lines fetched: 14,095 MinMaxDate << EQUIPMENT Lines fetched: 1 TempCal << AUTOGENERATE(149) Lines fetched: 149 Mastercalendar << TempCal Lines fetched: 149

$Syn 1 = Barcode+Username+Inspection Time+Item Type+Building+Floor+Location+Room Number+Description+Comments+Pass+Failure Mode+DateAdded+DateRetired+ReasonRetired+Make+Manufacturer+ModelNo+InspectionFrequency+New Barcode+Source

$Syn 2 = Barcode+Username+Inspection Time+Item Type+Building+Floor+Location+Room Number+Description+Comments+Pass+Failure Mode+DateAdded+DateRetired+ReasonRetired+Make+Manufacturer+ModelNo+InspectionFrequency+New Barcode+Source+Get Date

$Syn 3 = Barcode+Username+Inspection Time+Item Type+Building+Floor+Location+Room Number+Description+Comments+Pass+Failure Mode+DateAdded+DateRetired+ReasonRetired+Make+Manufacturer+ModelNo+OccupancyType+InspectionFrequency+New Barcode+Source

$Syn 4 = Barcode+Username+Inspection Time+Item Type+Building+Floor+Location+Room Number+Description+Comments+Pass+Failure Mode+DateAdded+DateRetired+ReasonRetired+Make+Manufacturer+ModelNo+OccupancyType+InspectionFrequency+New Barcode+Source+Get Date

$Syn 5 = $Syn 1+$Syn 3 $Syn 6 = $Syn 1+$Syn 2 $Syn 7 = $Syn 1+$Syn 2+$Syn 3+$Syn 4 $Syn 8 = $Syn 5+$Syn 6+$Syn 7 Creating search index Search index creation completed successfully

App saved

Finished with error(s) and/or warning(s)

0 forced error(s)

8 synthetic key(s)

petter · ‎2018-06-07

As I said in the other thread that you marked answered before I got to respond properly:

You should create two tables: one for INSPECTION making sure all inspection data are concatenated. Then you should make one table for INVENTORY making sure all inventory data are concatenated. Finally make sure to create a Key by doing a concatenation of the fields that are appropriate.