topic Re: How do I remove synthetic key in the below query? in QlikView

How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

Tried renaming fields, still gives synthetic key for below query:

//Load Incremental Data

Incremental_Table:

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_STRING,

TIME_STRING,

DATE_TIME_STRING,

USER_NAME,

Date(Date#(DATE_STRING,'YYYYMMDD'),'MM/DD/YYYY') as DATE;

SQL Select....

FROM "\\Glsdwcfs02002\qv\QvDev\SrcDocs\2.NAM_Dell\1.QVD\MOD3_Data_Source.QVD" (QVD)

Hours_Associate:

LOAD USER_NAME

FROM "\\Glsdwcfs02002\qv\QvDev\SrcDocs\2.NAM_Dell\1.QVD\MOD3_Data_Source.QVD" (QVD)

;

LEFT Join(Hours_Associate)

Hour_Information:

LOAD * Inline [

SUPERVISOR, USER_NAME, HOURS, DATE

Charla Hunter,'Eliga Dani' , 8 , 07/16/2018,

Charla Hunter,'Karen Ector' , 8 , 07/16/2018,

Charla Hunter,'Lilian Fares' , 8 , 07/16/2018,

];

Re: How do I remove synthetic key in the below query?

vishsaggi — Wed, 25 Jul 2018 18:03:59 GMT

What is your joining key between these two tables. You can rename your Username from Hours Associate table to a new name or the Date. LIke

Hour_Information:

LOAD *, USER_NAME as Name, DATE as InfoDate Inline [

SUPERVISOR, USER_NAME, HOURS, DATE

Charla Hunter,'Eliga Dani' , 8 , 07/16/2018,

Charla Hunter,'Karen Ector' , 8 , 07/16/2018,

Charla Hunter,'Lilian Fares' , 8 , 07/16/2018,

];

Re: How do I remove synthetic key in the below query?

sunny_talwar — Wed, 25 Jul 2018 18:05:52 GMT

May be this

//Load Incremental Data

Incremental_Table:

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_STRING,

TIME_STRING,

DATE_TIME_STRING,

USER_NAME,

Date(Date#(DATE_STRING,'YYYYMMDD'),'MM/DD/YYYY') as DATE,

USER_NAME&Date(Date#(DATE_STRING,'YYYYMMDD'),'MM/DD/YYYY') as UserDateKey;

SQL Select....

FROM "\\Glsdwcfs02002\qv\QvDev\SrcDocs\2.NAM_Dell\1.QVD\MOD3_Data_Source.QVD" (QVD)

Hours_Associate_Temp:

LOAD USER_NAME

FROM "\\Glsdwcfs02002\qv\QvDev\SrcDocs\2.NAM_Dell\1.QVD\MOD3_Data_Source.QVD" (QVD);

LEFT Join(Hours_Associate_Temp)

Hour_Information:

LOAD * Inline [

SUPERVISOR, USER_NAME, HOURS, DATE

Charla Hunter,'Eliga Dani' , 8 , 07/16/2018,

Charla Hunter,'Karen Ector' , 8 , 07/16/2018,

Charla Hunter,'Lilian Fares' , 8 , 07/16/2018,

];

Hours_Associate:

LOAD *,

USER_NAME&DATE as UserDateKey

Resident Hours_Associate_Temp;

DROP Table Hours_Associate_Temp;

DROP Fields USER_NAME, DATE From Hours_Associate;

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:14:10 GMT

Still gives the synthetic key,

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:32:28 GMT

It does not give the hours information I tried to join through inline.

Re: How do I remove synthetic key in the below query?

sunny_talwar — Wed, 25 Jul 2018 18:34:19 GMT

I am not sure what that means.... did you drop the Hours_Associate table also?

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:37:36 GMT

Nope, I did not drop the Hours_Associate.

The HOURS field do not show up in the straight table which had showed up earlier when the synthetic keys were generated.

Re: How do I remove synthetic key in the below query?

sunny_talwar — Wed, 25 Jul 2018 18:39:16 GMT

Image of the new data model? I have not dropped the Hours field from the script I shared... I wonder what happened?

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:43:08 GMT

It shows up in a list box, but when I use the same field in a straight table, it gives blanks.

Re: How do I remove synthetic key in the below query?

sunny_talwar — Wed, 25 Jul 2018 18:52:35 GMT

May be try this

Hours_Associate:

LOAD *,

USER_NAME&Date(Date#(DATE_STRING,'YYYYMMDD'),'MM/DD/YYYY') as UserDateKey

Resident Hours_Associate_Temp;

DROP Table Hours_Associate_Temp;

DROP Fields USER_NAME, DATE From Hours_Associate;

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:58:12 GMT

Errors out.

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 18:59:23 GMT

I need to load DATE_STRING into Hours_Associate_Table?

Re: How do I remove synthetic key in the below query?

vishsaggi — Wed, 25 Jul 2018 19:02:57 GMT

Small correction Sunny it should be just DATE right?

LOAD *,

USER_NAME&Date(Date#(DATE,'YYYYMMDD'),'MM/DD/YYYY') as UserDateKey

Re: How do I remove synthetic key in the below query?

sunny_talwar — Wed, 25 Jul 2018 19:07:57 GMT

My bad, vishsaggi‌ is right... use DATE instead of DATE_STRING... But like this

Hours_Associate:

LOAD *,

USER_NAME&Date(DATE, 'MM/DD/YYYY') as UserDateKey

Resident Hours_Associate_Temp;

DROP Table Hours_Associate_Temp;

DROP Fields USER_NAME, DATE From Hours_Associate;

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 19:17:14 GMT

No error, but HOURS field in straight table does not populate as the above screenshot.

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 19:25:31 GMT

Closed it, and then reloaded it after opening. Works now! Thanks Sunny!

Re: How do I remove synthetic key in the below query?

haneeshmarella — Wed, 25 Jul 2018 19:25:56 GMT

Thank you Vishwarath!