topic Re: Scripting in QlikView

Scripting

Mon, 17 Feb 2014 07:52:01 GMT

I have a table 1. In this table i have following fields,

year,

Month,

Link_Id,

Qty,

Value

And In table 2 , I have Following

Date

Qty

Value

Link_Id.

But , In table 1 , I have unique Link_Id with their respective quantity. In table 2 , I have Link_Id Giving me further bifurcation of that quantity of particular Link_Id date wise.

Table 1: Table 2 :

Link_Id 10001 Link_Id Date Qty Value

Year 2013 10001 1/1/2012 4 20

Month jan 10001 3/4/2012 9 45

Qty 20 10001 31/12/2012 7 35

Value 100

Now How can i join this table . I need to show data long with their further bifurcation.

Thanks in advance.

Re: Scripting

Peter_Cammaert — Mon, 17 Feb 2014 08:07:05 GMT

What is the purpose of both tables? Is table 1 a current stock list? Then in both tables create a key composed of Year-Month-ProductID and rename all stock fields that overlap with the Sales (table 2?). Leave both tables separate but linked.

You do not want to join them if you don't want multiplication of stock quantities and amounts... (in a JOINed table, you won't ever get a correct stock quantity again by way of sum())

Best,

Peter

Re: Scripting

aveeeeeee7en — Mon, 17 Feb 2014 08:08:49 GMT

Hi Sandeep

There are many ways to Join Tables:

1) As Peter said create key and join the Tables.

2) Concatenate both the Tables and create Same Name Flag in both Tables.

Example:

Table1:

LOAD Link_Id,

Year,

Month,

Qty,

Value,

'Table1' AS Flag

FROM

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD Link_Id,

Date,

Qty,

Value,

'Table2' AS Flag

FROM

(ooxml, embedded labels, table is Sheet1);

See the Attached Files.

Hope it will be helpful for you.

Regards

Aviral Nag

Re: Scripting

Colin-Albert — Mon, 17 Feb 2014 08:12:11 GMT

Do you actually need table 1? It looks like you can derive all of the results from table 2. By summing the qty and value, and using month() and year() to calculate the date parts.

Re: Scripting

Anonymous — Mon, 17 Feb 2014 08:19:19 GMT

Hi,

I could not understand your actual requirement.

It seems that you dont actually require table 1, as all measures and dimensions can be achieved.

Is your real scenario different from the example you have provided??

If yes then please provide that one with sample data , it would help us in understanding what you actually require.

Regards

Nitin