Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrew_INEOS
Contributor III
Contributor III

Option for SQL instead of GUI?

Hi All,

I'm just getting to grips with what QCDI can do. We're using SAP, QCDI, and Snowflake (if that makes any difference).

QCDI can obviously alias all the data by editing each field within the GUI. But is there a way to simply drop in a SQL script instead? I've already got all of my aliases written in that format so it would be a big time saver if I could drop that in. Editing one field at a time would get exhausting, not to mention be more prone to error.

Is there also a dynamic table option in QCDI like there is in Snowflake? If so, I could just bring in my data dictionary with the following fields;

[SAP_FIELDNAME], [REPORTING_FIELDNAME]

And then let that do all the aliasing work for me.

How are other businesses operating when they have both QCDI and Snowflake?

I'm contemplating using Snowpipe to do it all, that requires the data be landing in a stage; which is what happens in the Onboarding process in QCDI. But does Snowpipe have a higher cost than QCDI as it's credit based?

Thanks in advance

1 Solution

Accepted Solutions
TimGarrod
Employee
Employee

Hey Andrew - there are 2 ways to handle this...

1. -there is a way to provide a CSV of field names and output names as a renaming RULE.    If you look at the 'TRANSFORMATION RULES' you will see the ability to define a naming dictionary (you can import CSV there).   This prevents the need to do one at a time. 

TimGarrod_0-1717171796593.png

 

TimGarrod_1-1717171807827.pngTimGarrod_2-1717171824768.png

 

I would prefer this method above as it would allow the change to be done in Storage or transform layers via metadata and you don't need to manage a bunch of SQL statements to perform the translations.

2. After ingestion you can drop a SQL statement in using the TRANSFORM task and custom SQL.    More manual here though - so I would do the above.

 

 

Note - if you have very explicit rules for each table - you may want a rule filtered per object with an upload of the CSV for each.  You can also define a GLOBAL set of translations (rule stack ontop of each other) if you want MANDT to be 'Client' for example in every table. 

 

Hope this helps!

View solution in original post

2 Replies
TimGarrod
Employee
Employee

Hey Andrew - there are 2 ways to handle this...

1. -there is a way to provide a CSV of field names and output names as a renaming RULE.    If you look at the 'TRANSFORMATION RULES' you will see the ability to define a naming dictionary (you can import CSV there).   This prevents the need to do one at a time. 

TimGarrod_0-1717171796593.png

 

TimGarrod_1-1717171807827.pngTimGarrod_2-1717171824768.png

 

I would prefer this method above as it would allow the change to be done in Storage or transform layers via metadata and you don't need to manage a bunch of SQL statements to perform the translations.

2. After ingestion you can drop a SQL statement in using the TRANSFORM task and custom SQL.    More manual here though - so I would do the above.

 

 

Note - if you have very explicit rules for each table - you may want a rule filtered per object with an upload of the CSV for each.  You can also define a GLOBAL set of translations (rule stack ontop of each other) if you want MANDT to be 'Client' for example in every table. 

 

Hope this helps!

Andrew_INEOS
Contributor III
Contributor III
Author

Thanks Tim. I didn't even know that was an option until I followed your advice and went through 4-5 clicks. Can I make a couple of suggestions to Qlik design staff?

  • Signpost better. Even changing the first option to "Rename Column(s)" instead of "Rename Column" would imply I can do more than one at a time.
  • Can we have an option to point to a different table or insert some SQL as an alternative option to uploading a CSV? My Data dictionary is stored in the database so it would be easier. Extracting that and then uploading 1 CSV per table is certainly better than renaming every single field, but it still feels a step removed from ideal.