topic Re: Multiple connection String in QlikView

Multiple connection String

Mon, 22 Dec 2014 08:45:26 GMT

Dear all,

I have two connection string. one with Oracle and another one with Sql Server. By using both connection i want to show data in one table box. but problem is when i wants to show data in one table box that time duplicate data comes. For your better understanding i have attached my file.

Regards,

Masba

Re: Multiple connection String

saumyashah90 — Mon, 22 Dec 2014 08:55:01 GMT

Hi Masba,

This will surely create duplicate results because you have two different values from both the tables hence resulting in duplication.

to resolve this you have two options

1)Either you check both should have same data for same name of fields and same combination/association with other columns or fields.

2)Or, if you want to add the values based on brand names or category names,you will have to change your script by using group by function

Re: Multiple connection String

Michiel_QV_Fan — Mon, 22 Dec 2014 08:58:37 GMT

If I sort your data Brand name I do not see any duplicates.

It is a best practice to eliminate the synthetic keys with a surrogate key.

Re: Multiple connection String

lironbaram — Mon, 22 Dec 2014 09:01:03 GMT

hi your preload table

C_DEPOT_SALES_VALUE_APEX_IT

isn't connected to the other data

you need to change your load script to something like

Load *,

CT.BRAND_CODE AS Brand_Id,

CT.BRAND as Brand_Name,

CT.MAIN_CODE aS Category_Id,

CT.CATEGORY_DESC as Category_name ;

select CT.BRAND ,CT.BRAND_CODE ,CT.CATEGORY_DESC ,CT.MAIN_CODE , sum(dsv.SALES_QTY) Depot_Pre_Sales_Qty,

sum(DSV.SALES_VALUE) Depot_Pre_SALES_VALUE from C_DEPOT_SALES_VALUE_APEX_IT dsv , C_APEX_IT_PART_CATEGORIZE CT

where dsv.INVOICE_DATE >= add_months( TO_DATE(('$(vReFromDate)'), 'MM/DD/yyyy'), -12 )

and dsv.INVOICE_DATE <= add_months( TO_DATE(('$(vReToDate)'), 'MM/DD/yyyy'), -12 )

and DSV.PART_NO=CT.PART_NO group by

CT.BRAND,CT.BRAND_CODE,CT.CATEGORY_DESC,CT.MAIN_CODE

order by CT.BRAND_CODE,CT.MAIN_CODE;

Re: Multiple connection String

Mon, 22 Dec 2014 09:03:47 GMT

Dear Saumya,

Thanks for ur reply.. I have already consider your second option in my script but problem is same..pls help me...

Re: Multiple connection String

Mon, 22 Dec 2014 09:11:07 GMT

Dear Liron,

As per your sugesstion i have change my script but faceing problem... After run my script one error come. please check attachment.

Masba

Re: Multiple connection String

aveeeeeee7en — Mon, 22 Dec 2014 09:11:56 GMT

First, Concatenate 2 tables and than Use CommonFlag field in it. See example:

Table1: // Data Source Oracle

LOAD

'ORACLE' AS CommonFlag,

....

FROM XYZ;

CONCATENATE(Table1)

Table2: // Data Source SQL Server

LOAD

'SQL' AS CommonFlag,

....

FROM XYZ;

After Reload, Use the field CommonFlag and use it in Chart (Straight Table or Pivot Chart). See Example:

1) For Oracle Table:

Dimension: if(CommonFlag='ORACLE',CommonFlag) than check the option suppress when value is null :

Use other dimension and your expression.

Follow the same above steps for SQL Server.

Dimension: if(CommonFlag='SQL',CommonFlag) than check the option suppress when value is null.

You can also hide this calculated field in Straight in case if you don't want to show it to users.

Regards

Av7eN

Re: Multiple connection String

Mon, 22 Dec 2014 09:19:23 GMT

Please Load data sheets