Dimemsion Sorting Issue

[tracycrown]

Dear all

I am unable to sort dimensions (SKU_From & SKU_To) in Table 2 as same load order as SKU (Burger, Fries, Coke, Salad & Coffee) in Table 1, please advise me urgently.

Also, how to resolve synthetic keys in the script.

Thank you very much, Tracy

[Daniel_Castella · Accepted solution]

Hi @tracycrown 

You are having a synthetic key because two tables are being related with more than one field. In order to correct it, there are different approaches. My recommendation would be to concatenate both tables like you will see in the code below. You will see too that I use a field called Origin to distinguish which data correspond to which concatenation. 

Other option is to create a single field combining both, like Transaction&SKU as Link Field. But in a so small datamodel like the one in the app, I think it is better the other option.

To solve the sorting issue, instead of having a separate table for SKU#, I performed a left join. In this way you can use SKU# to perform an Order By in your transformations. 

The modified script I used is:

// Raw Transaction Data
Transaction_SKU:
CrossTable(SKU, Data)
LOAD * FROM
[QV-Test.xlsx]
(ooxml, embedded labels, table is QV2);

LEFT JOIN(Transaction_SKU)
LOAD SKU#,
SKU
FROM
[QV-Test.xlsx]
(ooxml, embedded labels, table is SKU_List);

// Keep Only Purchased SKUs
Transaction_SKU_Clean:
LOAD
Transaction,
SKU,
'Clean' as Origin
RESIDENT Transaction_SKU
WHERE Data = 1
ORDER BY SKU#;

SKU_Pairs:
LOAD
Transaction,
SKU AS SKU_From
RESIDENT Transaction_SKU_Clean;

JOIN (SKU_Pairs)
LOAD
Transaction,
SKU AS SKU_To
RESIDENT Transaction_SKU_Clean;

Final_SKU_Pairs:
LOAD
SKU_From,
SKU_To
RESIDENT SKU_Pairs
WHERE SKU_From <> SKU_To;

SKU_Matrix_Fact:
LOAD
SKU_From,
SKU_To,
Count(SKU_From) as Frequency
// Count(*) AS Frequency
RESIDENT Final_SKU_Pairs
GROUP BY SKU_From, SKU_To;

DROP TABLE SKU_Pairs;
DROP TABLE Final_SKU_Pairs;

SKU_Frequency:
LOAD
SKU AS SKU_From,
Count(DISTINCT Transaction) AS SKU_Count
RESIDENT Transaction_SKU_Clean
GROUP BY SKU;

//NEW
LEFT JOIN (SKU_Matrix_Fact)
LOAD
SKU_From AS SKU_To,
SKU_Count AS SKU_To_Count
RESIDENT SKU_Frequency;

CONCATENATE(Transaction_SKU_Clean)
LOAD *, 'RAW' as Origin
RESIDENT Transaction_SKU;

DROP TABLE Transaction_SKU;

The results:

Let me know if this works for you.

Kind Regards

Daniel

[tracycrown]

Hi Mr Daniel_Castella

Thank you so much for your kind advice, you are very knowledgeable and experienced.

Based on the correct sorting in Table 2, may i know how to present a report or table to show the combination of each pair as I would like to plot them in a bar chart

For example :

Pair                       Result

Burger, Burger        0.00

Burger, Fries           0.70

Burger, Coke           0.70 etc

Thank you very much, Tracy

[Daniel_Castella · Accepted solution]

Hi @tracycrown 

In order to display the double pairs Burger, Burger; Fries, Fries; ... few changes are needed in the script and in the tables since currently you are excluding these cases.

The script would be the following:

// Raw Transaction Data
Transaction_SKU:
CrossTable(SKU, Data)
LOAD * FROM
[QV-Test.xlsx]
(ooxml, embedded labels, table is QV2);

LEFT JOIN(Transaction_SKU)
LOAD SKU#,
SKU
FROM
[QV-Test.xlsx]
(ooxml, embedded labels, table is SKU_List);

// Keep Only Purchased SKUs
Transaction_SKU_Clean:
LOAD
Transaction,
SKU,
'Clean' as Origin
RESIDENT Transaction_SKU
WHERE Data = 1
ORDER BY SKU#;

SKU_Pairs:
LOAD
Transaction,
SKU AS SKU_From
RESIDENT Transaction_SKU_Clean;

JOIN (SKU_Pairs)
LOAD
Transaction,
SKU AS SKU_To
RESIDENT Transaction_SKU_Clean;

Final_SKU_Pairs:
LOAD
SKU_From,
SKU_To
RESIDENT SKU_Pairs;

SKU_Matrix_Fact:
LOAD
SKU_From,
SKU_To,
Count(SKU_From) as Frequency
// Count(*) AS Frequency
RESIDENT Final_SKU_Pairs
GROUP BY SKU_From, SKU_To;

LEFT JOIN(SKU_Matrix_Fact)
LOAD SKU# as SKU#_From,
SKU as SKU_From
FROM
[QV-Test.xlsx]
(ooxml, embedded labels, table is SKU_List);

DROP TABLE SKU_Pairs,Final_SKU_Pairs;

SKU_Frequency:
LOAD
SKU AS SKU_From,
Count(DISTINCT Transaction) AS SKU_Count
RESIDENT Transaction_SKU_Clean
GROUP BY SKU;

//NEW
LEFT JOIN (SKU_Matrix_Fact)
LOAD
SKU_From AS SKU_To,
SKU_Count AS SKU_To_Count
RESIDENT SKU_Frequency;

CONCATENATE(Transaction_SKU_Clean)
LOAD *, 'RAW' as Origin
RESIDENT Transaction_SKU;

SKU_Matrix_Fact_Final:
NoConcatenate
LOAD
SKU_From & ', ' & SKU_To as Pair,
*
RESIDENT SKU_Matrix_Fact
ORDER BY SKU#_From;

DROP TABLE Transaction_SKU, SKU_Matrix_Fact;

The issue now is that the double pairs have values. Hence, if they need to be 0, the measure in the table needs to be changed to:

If(SKU_To=SKU_From, 0, SKU_Count / Count(DISTINCT TOTAL Transaction))

In this way we obtain:

I hope this helps.

Kind Regards

Daniel

[tracycrown]

Dear Mr Daniel_Castella

Sorry to disturb you again as I would like to see some changes in report or dashboard when I select different date range (eg. Year between 2010 and 2025), please advise how to change the script to meet the requirement.

Thank you so much, Tracy