Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Merge data queries

I am loading monthly sales volume by product from an OLE DB connection and then I have my gross margin by product in a csv file. I want to be able to multiply the gross margin for a product by its respective volume to create graphs and KPI's. Is there a way I can merge the queries in the data load editor?

I already tried just doing a calculated measure in the app overview and it isn't returning anything.

Here is what my script looks like right now (minus proprietary details )

//CSV Gross Margin Load

LOAD

    Product as "Product Num",

    GM_Per_Lb

FROM [lib://Gross Margin/Gross Margin.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//Sales History Load

LIB CONNECT TO '****************************************';

SQL SELECT Territory,

    "Bill To Name",

    "Ship To Name",

    "Ship Date",

    "Product Num",

    "Product Desc",

    "Actual Fill Weight",

FROM "DC59_Live".dbo."ADGV_SO_Territory"

LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"

ON "Product Num" = "en_prod_key";

Tags (2)
4 Replies
ganeshreddy
Contributor III

Re: Merge data queries

Hi Anderson,

Use Applymap() for this scenario.(Use Product number as a key). Avoid joining , use appymap() it may increase the performance.

Cheers,

Ganesh.

Not applicable

Re: Merge data queries

Hi,

Your measure calculated in the application should return a value, we should look for what is wrong here.

Anyway you can try this script :

MAP1:
MAPPING LOAD

    Product as "Product Num",

    GM_Per_Lb

FROM [lib://Gross Margin/Gross Margin.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LIB CONNECT TO '****************************************';

MyTable:
LOAD
   *,
   ApplyMap ('MAP1', GM_Per_Lb) AS GM_Per_Lb;

SQL SELECT Territory,

    "Bill To Name",

    "Ship To Name",

    "Ship Date",

    "Product Num",

    "Product Desc",

    "Actual Fill Weight",

FROM "DC59_Live".dbo."ADGV_SO_Territory"

LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"

ON "Product Num" = "en_prod_key";

Not applicable

Re: Merge data queries

This is looking very promising. When I include the code you provided Xavier_CH199‌‌ and run the debugger it gets to the "SQL SELECT Territory" and then throws the error "Field not found - <GM_Per_Lb>" do I need to do anything inside the SQL SELECT statement?

Not applicable

Re: Merge data queries

Okay I just got it, I had to put ApplyMap ('MAP1', "Product Num") AS GM_Per_Lb;  instead. Thanks Xavier and Ganesh for your help! I wouldn't have known to use ApplyMap without you.


Thanks!

Community Browser