Remapping parent fields to child

lukegilligan — Mon, 19 Feb 2018 04:36:55 GMT

I have a set of data that contains a "Parent" reference, that links back to the same table but a different record:

Customer:

LOAD

code,

name,

    oid AS myCustomer,

    isChildAccount,

    IF(myParent=0,'',myParent) AS myParent;

SQL SELECT

code,

name,

    oid,

    isChildAccount,

    myParent

FROM LoginSchema.Customer;

LEFT JOIN (Customer)

LOAD

getCustomer AS myCustomer,

    getJCJob AS myJCJob,

    myAssociatedJob,

    myHousingCRMOrganisation,

    getCRMOrganisation,

    oid AS myHousingCustomer;

SQL SELECT

getCustomer,

    getJCJob,

    myAssociatedJob,

    myHousingCRMOrganisation,

    getCRMOrganisation,

    oid

FROM LoginSchema.HousingCustomer;

I am wanting to populate everything from the parent record against the child record, but can't get my head around how to have the records loop back on themselves.

Any help would be much appreciated.

Thanks

Re: Remapping parent fields to child

petter — Mon, 19 Feb 2018 08:55:25 GMT

1. Solve it directly in QlikView or Qlik Sense by using the Hierarchy load prefix:

The syntax:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/Hierarchy.htm‌‌

Excellent articles by Qlik's own master Henric Cronström:

Hierarchies‌

Unbalanced, n-level hierarchies

2. Alternatively solve it with the source database's own syntax in the SQL statement:

This is a such a common challenge so all the larger SQL database vendors have provided a special syntax to solve this. Here is the general information about the topic: Hierarchical and recursive queries in SQL - Wikipedia

Oracle DB has the CONNECT BY: Oracle "connect by" syntax‌ or the WITH (CTE) statement: Oracle 12c Common Table Expressions
IBM DB2: Hierarchical Queries with DB2 Connect By‌ and Hierarchical Queries - IBM DB2 Wiki - IBM DB2 - Toad World
Microsoft SQL Server: Recursive Queries Using Common Table Expressions
PostgreSQL: https://www.postgresql.org/docs/current/static/queries-with.html
MySQL: An Introduction to MySQL CTE‌
Good article about the topic: https://www.linkedin.com/pulse/step-by-step-guide-creating-sql-hierarchical-queries-bibhas-mitra/

topic Re: Remapping parent fields to child in QlikView

Remapping parent fields to child

Re: Remapping parent fields to child