topic Re: Synthetic keys in App Development

Synthetic keys

tracycrown — Thu, 06 Oct 2022 13:34:32 GMT

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

Re: Synthetic keys

QFabian — Thu, 06 Oct 2022 19:30:10 GMT

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/synthetic-keys.htm

For the drill down, check this about groups :

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

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/DateAndTimeFunctions/quartername.htm

Re: Synthetic keys

tracycrown — Thu, 06 Oct 2022 15:56:36 GMT

Dear QFabian

Thank you so much for your quick response.

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

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

Re: Synthetic keys

QFabian — Thu, 06 Oct 2022 19:31:16 GMT

@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

Re: Synthetic keys

tracycrown — Fri, 07 Oct 2022 00:04:32 GMT

Dear QFabian

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

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

Thanks, Tracy

Re: Synthetic keys

QFabian — Fri, 07 Oct 2022 01:34:23 GMT

quartername

Re: Synthetic keys

tracycrown — Fri, 07 Oct 2022 03:47:39 GMT

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

Re: Synthetic keys

tracycrown — Sat, 08 Oct 2022 14:49:48 GMT

Dear QFabian

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

Many Thanks, Tracy