topic Re: comparing columns with the same values from two different tables in QlikView

comparing columns with the same values from two different tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi! Thank you so much for taking the time to read my question and potentially help me!

I have to create a dashboard that takes data from two different tables (two different months) and compare the respective sales where three columns match.

Brand: Location: Store#: Sales:

441 XX 1 2500

640 YY 1 3000

560 ZZ 2 2000

901 YY 3 400

678 CC 3 5000

So I have two tables like the one above (one for month1 and another for month2), and I need to see where the sales increased by 10% between the two months where the region, brand, and store# are the same. (ie, I am trying to compare the brands, seeing if any brand started selling more than I expected them to, and therefore need the store# and location to be the same). I have attached a QVW document as well, if that would help, but I am truly at loss of how to better do this.

I have read about using the key_ in the load script, but in a past time of trying to do this, it ended up increasing the sales number so I am very wary of this ...

Re: comparing columns with the same values from two different tables

aarkay29 — Thu, 23 Feb 2017 17:21:07 GMT

Concatenate fields to make a composite key:

Location&Store#&Brand as key

Re: comparing columns with the same values from two different tables

Anonymous — Thu, 23 Feb 2017 17:59:45 GMT

Thank you Aar!

So if my load script is the following, what would I do to that? How would I rewrite it?

LOAD Location as month1_location,
     Store# as month1_store1,
     Brand as month1_brand,
     Sales as month1_sales,
     Location as key_location,
     Store# as key_store1,
     Brand as key_brand
FROM ...;

LOAD Location as month2_location,
     Store# as month2_store1,
     Brand as month2_brand,
     Sales as month2_sales,
    Location as key_location,
     Store# as key_store1,
     Brand as key_brand
FROM ...

Re: comparing columns with the same values from two different tables

albertovarela — Thu, 23 Feb 2017 18:18:58 GMT

As a stating point here's what I'd recommend:

Load your data in a single fact table:

Sales:

LOAD

Sales,

Location,

Store#,

Brand,

'Month 2' as Month

FROM (ooxml, embedded labels);

Concatenate(Sales) //Append records

LOAD

Sales,

Location,

Store#,

Brand,

'Month 1' as Month

FROM (ooxml, embedded labels);

Then create a chart to compare Month 1 vs 2

Re: comparing columns with the same values from two different tables

aarkay29 — Thu, 23 Feb 2017 18:38:23 GMT

Month1:

LOAD

Sales as SalesMonth1,

Location&Store#&Brand as key,

Store# as month1_store1,

Brand as month1_brand,

Location as Location1_sales

FROM (ooxml, embedded labels);

Month2:

LOAD

Sales as SalesMonth2,

Location&Store#&Brand as key,

Store# as month2_store1,

Brand as month2_brand,

Location as Location2_sales

FROM (ooxml, embedded labels);