Synthetic keys

[tracycrown]

Dear Sir Can someone help me to resolve the followings: 1. synthetic keys (Part# and Date) 2. Drill down date by year, quarter, month, week and day Thank you, Tracy

[QFabian]

Hi @tracycrown , try this, it will create a union table, using a composite field, and the desired fields :

Qlik Help about synthetic keys.

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/synthet...

For the drill down, check this about groups :

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Hierarchic_groups...

Script:

Receiving:
LOAD
Part# &' | ' & Date as %_Key,
Invoice# as P_Invoice#,
Quantity as P_Quantity,
Total as P_TotalAmt,
Unit_Cost,
Vendor#
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Receiving);

Issuing:
LOAD
Part# &' | ' & Date as %_Key,
Invoice# as S_Invoice#,
Quantity as S_Quantity,
Total as S_TotalAmt,
Unit_Price,
Customer#
FROM
[Test Data.xlsx]
(ooxml, embedded labels, table is Issuing);

Union_Aux:
Load distinct
%_Key
Resident Receiving;
Load distinct
%_Key
Resident Issuing;

Union:
Load distinct
%_Key,
subfield(%_Key, ' | ', 1) as Part#,
date(subfield(%_Key, ' | ', 2)) as Date,
year(subfield(%_Key, ' | ', 2)) as Year,
quartername(subfield(%_Key, ' | ', 2)) as Quarter,
Month(subfield(%_Key, ' | ', 2)) as Month,
day(subfield(%_Key, ' | ', 2)) as Day
Resident Union_Aux;

drop table Union_Aux;

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

[tracycrown]

Dear QFabian

Thank you so much for your quick response.

I do not understand why YEAR (Subfield(%_Key) for Quarter, Month and Day

date(subfield(%_Key, ' | ', 2)) as Date,
year(subfield(%_Key, ' | ', 2)) as Year,
year(subfield(%_Key, ' | ', 2)) as Quarter,
year(subfield(%_Key, ' | ', 2)) as MOnth,
year(subfield(%_Key, ' | ', 2)) as Day
Resident Union_Aux;

Also, it looks funny to replace Part# with % Key, see attached below.

[QFabian]

@tracycrown  Please check the updated post.

The fields where recreated in the UNION table.

It supossed to be year, quarter, month and day functions, i forgot to do that 

[tracycrown]

Dear QFabian

Thank you so much for your clarification. Please note that quarter no working :

Quarter(subfield(%_Key, ' | ', 2)) as Quarter,

Thanks, Tracy

[QFabian]

quartername

[tracycrown]

Dear QFabian

Please help to answer the following :

1. May I know why 1st row for Part# is blank

2. How to change Quartername (Jan-Mar 2021) to Q1-2021

Thank you, Tracy

[tracycrown · Accepted solution]

Dear QFabian

May be you are too busy to answer above queries but I have resolved them today. 

Many Thanks, Tracy