topic creating a new table in QlikView

creating a new table

Anonymous — Wed, 29 Mar 2017 12:00:32 GMT

Hello 🙂

I have two tables with invoice data:

Invoice:

LOAD

IV,

PART, //PAYPART

QUANT as PAYQUANT

InvoiceDetailed:

LOAD

IV,

PART, //ACTUALPART

QUANT as ACTUALQUANT

for example:

Invoice table:

IV PART PAYQUANT

19 XXX 15

19 ZZZ 13

InvoiceDetailed table:

IV PART ACTUALQUANT

19 XXX 15

19 YYY 22

What I need is to have a final table with all Possibilities:

IV PART PAYQUANT ACTUALQUANT

19 XXX 15 15

19 ZZZ 13 0

19 YYY 0 22

I would appreiciate your help in finding a solution.

Re: creating a new table

Anil_Babu_Samineni — Wed, 29 Mar 2017 12:32:50 GMT

What is the tabular you are getting by using Join?

[Invoice table]:

LOAD * Inline [

IV, PART , PAYQUANT

19, XXX , 15

19, ZZZ, 13

];

Join

[InvoiceDetailed table]:

LOAD * Inline [

IV , PART , ACTUALQUANT

19 , XXX , 15

19 , YYY, 22

];

Re: creating a new table

Anonymous — Wed, 29 Mar 2017 12:39:18 GMT

Try something like

Invoice:

Load * inline [

IV,PART,PAYQUANT

19,XXX,15

19,ZZZ,13

];

InvoiceDetailedtable:

Load * inline [

IV,PART,ACTUALQUANT

19,XXX,15

19,YYY,22];

join(Invoice)

load * Resident InvoiceDetailedtable;

drop table InvoiceDetailedtable;

Re: creating a new table

Anonymous — Thu, 30 Mar 2017 05:03:47 GMT

you can see that i have two keys - IV and APRT.

Therefore join does not work.

I have also made table of keys, and it didn't work as well since there are different shows in table invoice and table InvoiceDetailed, So the result os always only the parts from invoice.

Re: creating a new table

Anil_Babu_Samineni — Thu, 30 Mar 2017 05:08:22 GMT

But, As per your output we get same

Re: creating a new table

Anonymous — Thu, 30 Mar 2017 05:14:24 GMT

If I understood correct, you are offering me to make the table using inline.

However, this is data that is being loaded from an external data base.

So inline is not an option.

Re: creating a new table

Anonymous — Thu, 30 Mar 2017 10:12:45 GMT

Hi Ronit,

Either inline or pulled from database,, the join works the same.

Try loading the data as you are doing in the script and use

join(Invoice)

load * resident InvoiceDetailed;

and drop the table InvoiceDetailed;

you should be getting your output.

Re: creating a new table

Anonymous — Sun, 02 Apr 2017 06:44:11 GMT

The result I am getting is:

IV PART PAYQUANT ACTUALQUANT

19 XXX 15 15

19 ZZZ 13 0

it doesn't show me :

19 YYY 0 22

(I believe that because part YYY only appreas in table InvoiceDetailed)

Re: creating a new table

Anil_Babu_Samineni — Sun, 02 Apr 2017 06:49:41 GMT

If structure is same it should work. Inline just accepted the memory. it will work even external source data. Would you provide sample data in excel then let us know output. Data should real data

Re: creating a new table

Anonymous — Sun, 02 Apr 2017 07:03:02 GMT

HA HA!

You saying that structure should be the same OPENED MY EYES.

Thank you!!!

Re: creating a new table

Anil_Babu_Samineni — Sun, 02 Apr 2017 07:11:15 GMT

Hahaha, I shocked. Some what i gave good input to you. Now, you can close this thread by flag Correct Answer

Re: creating a new table

maxgro — Sun, 02 Apr 2017 07:11:29 GMT

maybe you have a different name for PART field in the 2 source tables, PAYPART and ACTUALPART;

you should rename PAYPART as PART and ACTUALPART as PART if you want the join on 2 fields: the join work if the fields has the same names

[Invoice Table]:

LOAD

IV, // join field

PAYPART as PART, // join field

QUANT as PAYQUANT;

SQL SELECT

IV,

PAYPART,

QUANT

FROM

.............

;

JOIN ([Invoice Table])

LOAD

IV, // join field

ACTUALPART as PART, // join field

QUANT as ACTUALQUANT

SQL SELECT

IV,

ACTUALPART,

QUANT

FROM

.............

;