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 ??

 

--

Lokesh

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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..

    Like
  • 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. 

     

    Cheers,

    Stuart

    Like 1
  • So now you know the inefficient way(my way), the correct way(Stuart's) and the recommended way(downstream) as well :)

    Like
  • There are many ways to do anything - I've had the luxury of getting it wrong in the past - always happy to help people shortcut my pain :-)

    Like
  • 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? 

     

    --

    Lokesh

    Like
  • So it doesnt look like you are going the recommended approach :). You can create a table schema to have an ETLDATE column. Then your insert statement would be "insert into common table select <common fields>, today() from Item_A group by common fields;" and so on...

    Like
  • Weekly Item Build process contains: Full Refresh. 

    File_A to Item_A

    File_B to Item_B

    File_C to Item_C

    Then Master_Item to be built using the above 3 Item tables based on Stuart's suggestion. 

     

    Thanks, 

    Lokesh 

    Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 7Replies
  • 296Views
  • 4 Following