topic Re: Problem joining tables in QlikView

Problem joining tables

mike_spada — Wed, 08 Jul 2015 16:33:13 GMT

Hi all,

I'm a newbie for the forum and I have an issue... maybe it's a simple problem but I'm going mad!

I have many tables, one is a master calendar for period and the others are summary tables in which I stored data from load script (I loaded many type of files which various data), in this way:

Periods:

Period
201411
201412
201501
201502

and the other is like this:

Data:

Period	CustomerCode	FileType
201411	A01	invoices.txt
201412	A01	invoices.txt
201501	A01	invoices.txt
201502	A01	invoices.txt
201411	B01	invoices.txt
201412	B01	invoices.txt
201502	B01	invoices.txt

I wish to build a situation similar to this:

Period	Customer	invoices.txt
201411	A1	LOADED!
201411	B1	LOADED!
201412	A1	LOADED!
201412	B1	LOADED!
201501	A1	LOADED!
201501	B1	MISSING!!!!!!!!!!
201502	A1	LOADED!
201502	B1	LOADED!

so I'll add some type of files and then I'll have a monitor of all loaded files per Customer. My problem is that I'm not able to show the row with missing value... because if I use different join in my script I lose that row... 😞

Can someone help me?

Thanks

Mike

Re: Problem joining tables

ger_alegria — Wed, 08 Jul 2015 17:44:44 GMT

Try with th next:

Period:

LOAD Period

FROM

Period.qvd

(qvd);

Join(Period)

LOAD Distinct

Customer

FROM

Master.qvd

(qvd);

STORE Period into PeriorCustomer.qvd;

DROP Table Period;

Master:

LOAD Period,

Customer

FROM

PeriorCustomer.qvd

(qvd);

Outer Join(Master)

LOAD Period,

Customer,

File

FROM

Master.qvd

(qvd);

STORE Master into MasterFinal.qvd;

DROP Table Master;

Final:

LOAD Period,

Customer,

if(isnull(File), 'MISSING','LOADED') as invoices

FROM

MasterFinal.qvd

(qvd);

And we will have:

Re: Problem joining tables

mike_spada — Wed, 08 Jul 2015 19:31:17 GMT

Gerardo,

thank you very much! It works... with one column (as I required)... but I want to have more columns, each for one file... so I tried to modify your good script in this way:

Period:

LOAD Period

FROM

Period.qvd

(qvd);

Join(Period)

LOAD Distinct

Customer

FROM

Master.qvd

(qvd);

Join(Period)

LOAD Distinct

FileType

FROM

Master.qvd

(qvd);

STORE Period into PeriodCustomer.qvd;

DROP Table Period;

Master:

LOAD Period,

Customer,

FileType as myFileType

FROM

PeriodCustomer.qvd

(qvd);

Outer Join(Master)

LOAD Period,

Customer,

FileType

FROM

Master.qvd

(qvd);

STORE Master into MasterFinal.qvd;

DROP Table Master;

Final:

LOAD Period,

Customer,

//if(isnull(FileType), 'MISSING','LOADED') as file, //no more needed

myFileType,

if(myFileType='invoices.txt',if(isnull(FileType), 'MISSING','LOADED')) as file_invoices,

if(myFileType='orders.txt',if(isnull(FileType), 'MISSING','LOADED')) as file_orders

FROM

MasterFinal.qvd

(qvd);

and the result is now what I wanted:

The expression behind the led is like

=if(file_invoices='LOADED','qmem://<bundled>/BuiltIn/led_g.png','qmem://<bundled>/BuiltIn/led_r.png')

you put me in the correct way... so thank you!

Have a nice day!

Mike

PS: if you see something to "best-practising" in my load script tell me 😉

Re: Problem joining tables

ger_alegria — Wed, 08 Jul 2015 19:46:22 GMT

Great Mike!!

Can you Mark te answer as helpful answer?

Have a nice day to!

Re: Problem joining tables

mike_spada — Wed, 08 Jul 2015 19:49:06 GMT

Not useful... correct 😉

Mike