topic SQL Syntax Help in QlikView

SQL Syntax Help

Thu, 20 Jan 2011 15:29:42 GMT

When you import tables from Excel and need to do a field name change you use an "as" function: for example

Division as "User Division"

What would the syntax be for changing field names from salesforce.come tables?

Please help!!

Thanx

SQL Syntax Help

Thu, 20 Jan 2011 15:34:51 GMT

Why don't you use preceding loads to do this alias change?

Like:

Load
A as Ca,
B as Cb
;
Load * From ...salesforce...;

SQL Syntax Help

Thu, 20 Jan 2011 15:37:24 GMT

Igor,

Thank you, but when I use the "as" to do an alias I get scripting errors. Is there another way?

Thanx

SQL Syntax Help

Thu, 20 Jan 2011 15:39:57 GMT

Some databases (like Oracle) doesn't accept AS

Just do

Select

Name1 Firstname,

Name2 Lastname

FROM

customer

SQL Syntax Help

Thu, 20 Jan 2011 15:42:24 GMT

Any experience with salesforce.com tables?

SQL Syntax Help

Thu, 20 Jan 2011 15:42:58 GMT

Please post your code. It will be easier for us to help.

SQL Syntax Help

Thu, 20 Jan 2011 15:45:56 GMT

Igor, Please note that the scripting error occurs when I make the two changes in the user table....I am trying to change "State to UserState and Division to User Division.

SQL SELECT A_R_OK__c,
A_R_Specialist_Collections__c,
Account__c,
Actual_Delivery__c,
Actual_SAD__c,
Additional_Items_Not_main_body__c,
AE__c,
Approved_for_Booking__c,
Apps_Start_Date__c,
Billing_Date__c,
Booked_on_AS400__c,
Booked_on_Flash__c,
BR_Order__c,
Change_Order__c,
City__c,
Complete_Package_Received__c,
ConnectionReceivedId,
ConnectionSentId,
Contact__c,
CreatedById,
CreatedDate,
CSE__c,
Custom_Terms__c,
Customer_Acceptance__c,
Customer_Called__c,
Customer_RAD__c,
Days_to_Book__c,
Deposit__c,
Deposit_Amount__c,
Deposit_Received__c,
Description__c,
Discount__c,
Email_Address__c,
Forecasted_Amount__c,
IC_PM__c,
Id,
Install_Start_Date__c,
Inventory_Ordered__c,
Invoice__c,
IsDeleted,
ISPM__c,
LastActivityDate,
LastModifiedById,
LastModifiedDate,
Main_Body_Total__c,
Name,
Opportunity__c,
Order_Entered_by__c,
Order_Incomplete_Explain__c,
Order_Incomplete_from_Sales__c,
Order_Packet_sent_to_OTR__c,
Order_Received__c,
Order_Rejected_by_OTR__c,
Order_Rejected_by_OTR_Explain__c,
Order_Type__c,
OTR_Specialist_Assigned__c,
Phone_Number__c,
PM_for_Synapse_orders__c,
PM_Release_Date__c,
PO_Date__c,
PO_Number__c,
PO_received_to_Transfer_to_OTR__c,
Process_Alert__c,
Product__c,
Projected_Release__c,
RAD_vs_Actual_Delivery__c,
Reason__c,
Reason_SAD_Missed__c,
RecordTypeId,
Remarks__c,
Resubmitted_to_OTR__c,
Revenue_Recognized__c,
RID__c,
SAD_vs_Actual_Delivery__c,
Ship_Date__c,
Shipped_Amount_Main_Body__c,
SR_Order__c,
State__c,
Status__c,
Submitted_for_Approval__c,
SystemModstamp,
Terms__c,
Total_Back_Order_Amount__c,
Total_Days_PO_to_Delivery__c,
Total_Order_Amount2__c,
Total_Shipped_Amount__c,
Type_of_Contract__c,
Under_Over_250K__c,
Zone__c
FROM Order__c;

SQL SELECT Accepted_Date__c,
AccountId,
Actual_Booked__c,
Actual_Commercial_Booking__c,
Additional_Notes_Exist__c,
Adequacy_of_Current_Postion__c,
Admin_training_completed__c,
Amount,
Annual_Amount__c,
Annual_Service__c,
Approved_By__c,
Assigned_Dealer__c,
Associated_Opportunity__c,
CampaignId,
Capital__c,
Capital_Lease__c,
Capital_Purchase__c,
Close_Date_Rev_Rec__c,
CloseDate,
Common_View__c,
Communication_Module__c,
Competition__c,
Competition_Type__c,
Config_Due__c,
Config_Received__c,
ConnectionReceivedId,
ConnectionSentId,
Contract_Effective_Date__c,
Contract_given_to_Customer__c,
Contract_Negotiation_Comments__c,
Contract_Terms__c,
Contract_Type__c,
ContractEndDate__c,
CreatedById,
CreatedDate,
Credit_Approval__c,
Credit_Comments__c,
Credit_Info_Received__c,
Credit_Info_Requested__c,
Credit_Risk__c,
CRM_Dealer__c,
CRm_Total__c,
CSS__c,
CSS_Comments__c,
Current_Sales_Obstacles__c,
Current_Volume__c,
Customer_RAD__c,
CV_Modalities_Quoted__c,
Data_Migrated__c,
DealLostReason__c,
Delivery_Date__c,
Demo_Completed__c,
Demo_Date_1__c,
Demo_Date_2__c,
Description,
End_User__c,
EST__c,
Exec_Visit_Completed__c,
Exec_Visit_Date__c,
Final_Booked_Amount__c,
Finance_Opportunity__c,
Financial_Analyst_Assigned__c,
First_Synapse_Sale__c,
Fiscal,
FiscalQuarter,
FiscalYear,
ForecastCategory,
Forecasted_for_Revenue__c,
FPS_Lease__c,
FUJI_Financial__c,
Go_Live_Date__c,
Government__c,
Hardware_Installed__c,
HasOpportunityLineItem,
HL7_Fees__c,
HL7_Interface_List__c,
HL7_Interface_Loaded__c,
Hospital_Credit_Approved__c,
Hospital_Credit_Grade__c,
iCAD_Total__c,
Id,
Install_Base_Program__c,
Install_Date__c,
Interface_s_tested__c,
Invoice_or_Contract_Notes__c,
IsClosed,
IsDeleted,
IsWon,
Kickoff_Call_s_or_Meeting__c,
Last_Action__c,
Last_Updated_BlueSheet__c,
LastActivityDate,
LastModifiedById,
LastModifiedDate,
Latest_Customer_Contact__c,
LeadSource,
MainCompetitor__c,
Managed_Services_Options__c,
Managers_Notes__c,
Managers_Notes_Exist__c,
Managers_Review_Date__c,
Market_Segment__c,
Migration__c,
My_Position_vs_Compeition__c,
Name,
New_Replacement__c,
Next_On_site_Visit__c,
NextStep,
Open_Issues__c,
Operations_Notes__c,
Opportunity_Type__c,
Order_2__c,
Other_Fees__c,
Overage__c,
OwnerId,
Pass_Through__c,
Percent_Implementation_Complete__c,
Pipeline_Category__c,
PO_Received__c,
Presentation_Completed__c,
Presentation_Date__c,
Price_Study__c,
Pricebook2Id,
Probability,
Product_Demo_d__c,
Professional_Services__c,
Promotion__c,
PS_Record_Id__c,
Quote_Due__c,
Quote_given_to_Customer__c,
Quote_Received__c,
RecordTypeId,
Red_Flags__c,
Red_line_Contract_Received__c,
Red_line_given_to_FMSU_Contracts__c,
RFP_0_250K__c,
RFP_250K_1M__c,
RFP_Assigned_to__c,
RFP_Due__c,
RFP_Notes__c,
RFP_over_1M__c,
RFP_Recieved__c,
RFP_Status__c,
RFPForce__CurrentGenerators__c,
RFPForce__DeliveryInstallationStatus__c,
RFPForce__MainCompetitors__c,
RFPForce__OrderNumber__c,
RFPForce__TrackingNumber__c,
Service_Contract_Type__c,
Signed_Lease_Docs_Received__c,
Site_Visit_Completed__c,
Site_Visit_Date__c,
Software_Installed__c,
StageName,
Strengths__c,
Synapse_Amount__c,
Synapse_Cardiology__c,
Synapse_Disaster_Recovery__c,
Synapse_IS__c,
Synapse_Managed_Services__c,
Synapse_Radiology__c,
Synapse_Radiology_PACS__c,
Synapse_RIS__c,
SystemModstamp,
Tables_Loaded__c,
Term_years__c,
Timing_of_Priorities__c,
Total_Amount__c,
Total_NST_Amount__c,
Total_Potential_Volume__c,
Total_Service_Contract_Amount__c,
Type__c,
Upfront_Capital__c,
X2nd_Tier_PE_Approval__c,
X2nd_Tier_PE_Approval_Date__c,
X2nd_Tier_PE_Comments__c,
X3D__c
FROM Opportunity;
SQL SELECT Alias,
CallCenterId,
City,
CompanyName,
ContactId,
contracts_coordinator__c,
Country,
CreatedById,
CreatedDate,
DelegatedApproverId,
Department,
Division,
Email,
EmailEncodingKey,
EmployeeNumber,
Extension,
Fax,
FirstName,
ForecastEnabled,
Id,
IsActive,
LanguageLocaleKey,
LastLoginDate,
LastModifiedById,
LastModifiedDate,
LastName,
LocaleSidKey,
ManagerId,
MobilePhone,
Name,
OfflinePdaTrialExpirationDate,
OfflineTrialExpirationDate,
Phone,
PostalCode,
ProfileId,
quote_approver__c,
ReceivesAdminInfoEmails,
ReceivesInfoEmails,
State as UserState,
Street,
SystemModstamp,
Territory__c,
TimeZoneSidKey,
Title,
Username,
UserPermissionsAvantgoUser,
UserPermissionsCallCenterAutoLogin,
UserPermissionsMarketingUser,
UserPermissionsMobileUser,
UserPermissionsOfflineUser,
UserPreferencesActivityRemindersPopup,
UserPreferencesDisableAutoSubForFeeds,
UserPreferencesEventRemindersCheckboxDefault,
UserPreferencesReminderSoundOff,
UserPreferencesTaskRemindersCheckboxDefault,
UserRoleId,
UserType,
Zone__c as UserZone
FROM User;

SQL Syntax Help

Thu, 20 Jan 2011 15:56:58 GMT

If u rename a field to have a space in it, u need to put brackets around it.

SELECT

Division [User division]

FROM

....

I recommend that u try to avoid spaces in fieldnames, try

SELECT

Division Userdivision

SELECT

Division User_division

Instead

SQL Syntax Help

Mon, 27 Feb 2012 16:20:39 GMT

I am having the same issue. I am trying to simply rename columns in my Qlikview script that are coming from my Salesforce.com instance.

However, when I try to run the usual "as" renaming in Qlikview, I get an error message. The below script does NOT work for me (since the data comes from Salesforce). Normally, this would pull data fine. as

----------------------------------------

SQL SELECT

Id,

DGA_Account_Number__c as [Customer_Number]

FROM Account;

I get this error message:

DGA_Account_Number__c as Customer_Number FROM Account

ERROR at Row:3:Column:26

unexpected token: 'as'

SQL SELECT

Id,

DGA_Account_Number__c as [Customer_Number]

FROM Account

------------------------------------

Any advice/guidance would help.

SQL Syntax Help

Anonymous — Mon, 27 Feb 2012 16:25:02 GMT

Hi,

did u try

Load

as Ca,

as Cb

SQL Syntax Help

Anonymous — Mon, 27 Feb 2012 16:29:08 GMT

Use This

[Division] as [User Division]

SQL Syntax Help

Mon, 27 Feb 2012 16:32:44 GMT

Neither of these methods seems to be working for me

SQL Syntax Help

Anonymous — Tue, 28 Feb 2012 07:15:58 GMT

the keyword AS is QV keyword not SQL , so bebore u run SQL statemen ( SQL SELECT) and after

can transform with QV

Try a script like this..

Tabele:

LOAD

XCDEL as XCDEL1,

capitalize(trim(XDTAB)) as XDTAB1;

SQL SELECT

XCDEL,

XDTAB

FROM .............;

SQL Syntax Help

jagan — Tue, 28 Feb 2012 07:23:51 GMT

Hi,

Use below script

TableName:

LOAD

Id AS ID,

[DGA_Account_Number__c] AS DGAAccountNumber;

SQL SELECT

Id,

DGA_Account_Number__c

FROM Account;

Regards,

Jagan.

SQL Syntax Help

Tue, 28 Feb 2012 13:17:48 GMT

That worked! Thank you all for your replies

SQL Syntax Help

jagan — Tue, 28 Feb 2012 13:24:31 GMT

Hi,

Close this Discussion by selecting "Correct Answer", if it solves your problem. It helps others to find the solution for this type of scenarios.

Regards,

jagan.

SQL Syntax Help

Tue, 28 Feb 2012 13:25:48 GMT

This was not my original question, so I cannot close this discussion as Answered