Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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! |
| B1 | LOADED! | |
| 201412 | A1 | LOADED! |
| B1 | LOADED! | |
| 201501 | A1 | LOADED! |
| B1 | MISSING!!!!!!!!!! | |
| 201502 | A1 | LOADED! |
| 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
Accepted Solutions
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:
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:
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 😉
