QlikSense help - Star Schema/Joins or ApplyMap

Question

I'm a new user to QLIK, scripting & overall beginner. I am looking for any help or recommendations to deal with my tables below. Just trying to create a good model to link my tables.

Created a sample here file The original 3 tables are different qvd files

1. Transactions table has multiple columns and the main ones are TxnID, SourcePartyTypeID, DestPartyTypeID, SourcePartyType, DestinationPartyType, ConductorID.
2. Customers Table - CustName, CustID etc.
3. Accounts Table - AcctID, AcctNum, PrimaryActID etc.

With transactions it can relate to multiple CustID's/AcctID's which are linked by the Dest/SourcePartyIDs. Also the transaction has a source/destination party type field where A = Accounts, C = Customers & some NULLs.

I have read a lot on data models and a link table for star schema or join is recommended but I am unsure how to code this because these are also based on the Source/DestinationType fields (Transactions Table) where A = Accounts & C = Customers. Have tried to code but not successful.

I'm unsure how to join based on SourceType/DestinationType = Accounts or Customers. Link table or ApplyMap() with a WHERE clause?? Any suggestions

Answer 1

Hopefully your introduction to Qlik is still a positive one! There are a lot of resources to help you develop your Qlik scripting capabilities including:

• Qlik Continuous Classroom (https://learning.qlik.com)
• Qlik Community (https://community.qlik.com)
• Qlik Product Documentation (https://help.qlik.com)

In terms of your sample data question. If you are creating a Qlik Sense app you can use the Qlik Data Manager to link your data.

This is excellent because not only will it try and analyse your data and make useful suggestions to link fields, it will also build the script which you can then review and use as a basis for developing your own understanding further.

Looking at your sample data, one option might be a simple key field between a couple of the tables. Here is one example of how this could work.

Rod

[Transactions]:
Load

// User generated fields
AutoNumberHash256 ( [DestPartyID], [SoucePartyID] )     As _keyAccount,

// Fields in source data
[TxnID],
[TxnNum],
[ConductorID],
[SourcePartyType],
[SoucePartyID]                                          As [CustID],
[DestPartyType],
[DestPartyID],
[etc...]

From [lib://AttachedFiles/TablesExamples.xlsx]
(ooxml, embedded labels, table is Transactions);


[Customers]:
Load

// User generated fields

// Fields in source data
[CustID],
[CustFirstName],
[CustLastName]

From [lib://AttachedFiles/TablesExamples.xlsx]
(ooxml, embedded labels, table is Customers);


[Accounts]:
Load

// User generated fields
AutoNumberHash256 ( [AcctID], [PrimaryAcctID] )         As _keyAccount,

// Fields in source data
[AcctID],
[AcctNum],
[PrimaryAcctID],
[AcctName]

From [lib://AttachedFiles/TablesExamples.xlsx]
(ooxml, embedded labels, table is Accounts);