UNION ALL function inside TQL
Hello TS Team,
I have processed three different Item Tables based on the Line of Business. But they do have some of the common attributes which can be combined and processed into a "Common Item Hierarchy" table. Is there a way I can build this using TQL using three Independent Item tables - Item_A; Item_B and Item_C ??
Hi Lokesh.. You can achieve this by a combination of Select and Insert statements:
Select <ColumnNames> into "Common Item Hierarchy" from Item_A;
//This will create a table called "Common Item Hierarchy" with the columns that are after Select from Item_A table.
Next, you can do
Insert into "Common Item Hierarchy" select <ColumnNames> from Item_B;
Insert into "Common Item Hierarchy" select <ColumnNames> from Item_C;
This will result in a table "Common Item Hierarchy", with values from each of the three different tables.
Hope this helps..
I would make one small change to this process. The problem here is that if you've got common values between the three tables and you do it this way you'll end up with multiple rows for that value resulting in overcounting. I'd also group by the full field set to reduce the number of rows being inserted (assuming the common table values could exist in many rows in the source) - otherwise you get the same overcounting problem.
You can get around this if you define a primary key - that way any rows you insert from the second and third tables with the same key will update rather than create new rows and you'll get a common dimension. In addition you may have sharded your facts but want this dimension to be replicated - so I'd define the table up front before doing any inserts. The process then becomes:
1. Define common table
2. Define primary key and joins
3. insert into common table select common fields from Item_A group by common fields;
4. insert into common table select common fields from Item_B group by common fields;
5. insert into common table select common fields from Item_C group by common fields;
This is fine for testing but we'd generally recommend that data manipulation happen downstream of ThoughtSpot rather than processing inside the system.
Great suggestions. It has to be a consistent incremental load process I need to design/Build a common Item dimension table across each LOB. Let me give it a try.
If I understand INSERT works like an UPSERT if the PK is already defined on the TARGET table.?
Is there a system date function to populate ETL_UPD_DATE for each incremental load process?