Remapping parent fields to child

[lukegilligan]

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

[petter]

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...‌‌

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/