I have data that I am pulling from my Org's Sharepoint using a few REST API calls in a script. I was able to make that script run and store data as a QVD Dataset.
Is there a way that I can push that directly in to a table I've created in my On-Prem SQL Server Database? I already have an established connection to load data from SQL in to QLIK, but now I want to PUSH instead of PULL
Daniele - Principal Technical Support Engineer & SaaS Support Coordinator at Qlik If a post helps to resolve your issue, please accept it as a Solution.
I assume you are doing it from Qlik Sense Enterprise on Premise environment. If thats the case there is no need of stored procedures and there is a simple way of doing it, however there are some admin settings/changes which must be done prior:
Once you do above you may use below code and adjust it to your needs. I used below code to connect to Qlik Sense Postgres Repository and I have inserted records with it. This is just very simple subroutine which reads existing data then loops through required records and creates column names and values and then passes them when you call subroutine. In your case I would suggest creating some unique ID for every row you want to insert (something like RowNo() ) so then you can loop through every unique value of it to pickup all column values for insert of particular row.
// Sub Insert CustomProperty Attribute
/* Parameters
zArea - Stream or User
zCP_1 - Custom Property 1 (used in where clause)
zCP_2 - Custom Property 2 (to be inserted)
*/
Sub Insert(zArea,zCP_1,zCP_2,zExecute)
LIB CONNECT TO 'QSR';
// Get Users Or Streams
Switch zArea
Case 'User'
Let vParam = 'User_ID';
Case 'Stream'
Let vParam = 'Stream_ID';
EndSwitch
// Obtain records for INSERT
[CustomPropertyValues]:
LOAD
ID as CustomPropertyID,
Value,
App_ID,
Stream_ID,
User_ID,
Definition_ID
Where
Len(Trim([$(vParam)])) > 0
and Value = '$(zCP_1)'
;
SELECT
"ID",
"Value",
"App_ID",
"Stream_ID",
"User_ID",
"Definition_ID"
FROM
"public"."CustomPropertyValues"
;
// Build parameters
Let i = 1;
For each vField in FieldValueList('$(vParam)')
Trace *********;
Trace i = '$(i)';
Let i = '$(i)'+1;
SET Fld_Name_1 = 'ID';
// this needs particular format of the 20ad4c2c-4fc7-4899-963b-e3ad437df156
Let vRand = Floor((Rand()*12345*1000))*Floor(Rand()*157*'$(i)');
Trace vRand '$(vRand)';
Let Fld_V_1 = Left('$(vRand)',4);
Let Fld_V_2 = Right('$(vRand)',3);
Let Fld_V_3 = Right(Left('$(vRand)',4),3);
Let Fld_V_4 = Right(Left('$(vRand)',3),2);
Let Fld_Value_1 = '$(Fld_V_4)ad4c2c-4fc7-$(Fld_V_1)-963b-e3ad$(Fld_V_3)df$(Fld_V_2)';
Trace ---------------------;
Trace Field '$(Fld_Name_1)' Insert with Parameter '$(Fld_Value_1)';
SET Fld_Name_2 = 'Value';
Let Fld_Value_2 = '$(zCP_2)';
Trace ---------------------;
Trace Field '$(Fld_Name_2)' Insert with Parameter '$(Fld_Value_2)';
SET Fld_Name_3 = 'Definition_ID';
Let Fld_Value_3 = Lookup ('Definition_ID','Value','$(zCP_1)','CustomPropertyValues') ;
Trace ---------------------;
Trace Field '$(Fld_Name_3)' Insert with Parameter '$(Fld_Value_3)';
SET Fld_Name_4 = 'CreatedDate';
Let Fld_Value_4 = TimeStamp(Now(),'YYYY-MM-DD hh:mm:ss.ffffff');
Trace ---------------------;
Trace Field '$(Fld_Name_4)' Insert with Parameter '$(Fld_Value_4)';
SET Fld_Name_5 = 'ModifiedDate';
Let Fld_Value_5 = TimeStamp(Now(),'YYYY-MM-DD hh:mm:ss.ffffff');
Trace ---------------------;
Trace Field '$(Fld_Name_5)' Insert with Parameter '$(Fld_Value_5)';
SET Fld_Name_6 = 'ModifiedByUserName';
Let Fld_Value_6 = 'INTERNAL\sa_repository';
Trace ---------------------;
Trace Field '$(Fld_Name_6)' Insert with Parameter '$(Fld_Value_6)';
SET Fld_Name_7 = '$(vParam)';
Let Fld_Value_7 = '$(vField)';
Trace ---------------------;
Trace Field '$(Fld_Name_7)' Insert with Parameter '$(Fld_Value_7)';
// Execute Insert
Switch zExecute
Case 'Yes'
SQL INSERT INTO "public"."CustomPropertyValues" ("$(Fld_Name_1)", "$(Fld_Name_2)","$(Fld_Name_3)","$(Fld_Name_4)","$(Fld_Name_5)","$(Fld_Name_6)","$(Fld_Name_7)")
VALUES ('$(Fld_Value_1)','$(Fld_Value_2)','$(Fld_Value_3)','$(Fld_Value_4)','$(Fld_Value_5)','$(Fld_Value_6)','$(Fld_Value_7)')
!EXECUTE_NON_SELECT_QUERY;
Case 'No'
Default
EndSwitch
Next
EndSub
// END
/* Insert new
Parameters:
* Stream or User
* Search for Existing Group
* Insert Group Name
* (Yes) - exceute or (Empty or "No) - Dry run */
Call Insert ('User','HR Testing','UAT','Yes');
Cheers
Lech
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
