Working with WHERE statements in QV load script

[#1]

Good afternoon all,

I have created a new QV doc but uploading five separte spreadsheets...so far so good.

I created three separate expression in the doc to generate the desired results:

• Has Active CDs:

=IF(([Has Active CDs]='Y'),1,0)

• Has Merged Reports:

=IF(([Bank Status]='Failed' or [Bank Status]='Merged') AND ([Merged Reports]='N'))

• Has Over $1,000,000.00:

=IF(([Bank Status]='Failed' or [Bank Status]='Merged') AND ([Recip Balance]>=1000000))

Each worked and generated the expected number of records each:

• 1558
• 7
• 24

respectively. But I wanted to concatenate these results to get a total list of 1589 records.

I did not understand that QV would apply each of these expressions as a filter, drilling down to the number of records that met all three requiremenst (four records); rather than concatenating them.

So I thought that maybe I could do this in the load script. So I added the following to the end of the script:

WHERE ([Has Active CDs]='Y')

OR (([Bank Status]='Failed' OR [Bank Status]='Merged') AND ([Merged Reports]='N'))

OR (([Bank Status]='Failed' OR [Bank Status]='Merged') AND ([Recip Balance]>='1000000'));

I quickly learned that, that would not work. I am loading six separate spreadsheets and the expression seems to only run against the document loaded immediately before it in the script.

I tested this by loading the WHERE ([Has Active CDs]='Y') portion after the spreasheet containing that column. This worked fine generating the expected 1558 records.

But the other two expressions are a problem for me as they each compare columns in two separate spreadsheets.

So, how do I do that?

Following is the current script, complete with the where statement above:

______________________________________________________________________________________

Directory;
LOAD [Bank ID],
     if(WildMatch([Bank Status],'Active','Failed','Merged'),[Bank Status]) AS [Bank Status]
FROM
[GL Compliance Report (2770).xlsx]
(ooxml, embedded labels, table is REPORT_2770);

Directory;
LOAD [Institution ID] as [Bank ID],
     Status as [Status-2790],
     Territory as [Territory-2790]
FROM
[CDARS Merged Banks Report (2790).xlsx]
(ooxml, embedded labels, table is REPORT_2790);

Directory;
LOAD [Prom ID] as [Bank ID], 
     [PIN Rating],
     [Capital Category],
     Num([Recip Balance],0.00) as [Recip Balance]
FROM
[SLX Query.xlsx]
(ooxml, embedded labels, table is [CDARS Banks w Balance]);

Directory;
LOAD IF([Assigned Caller]='ZebraFish',[Assigned Caller]) as [Assigned Caller],
     [Inst ID] as [Bank ID],
     IF([Disagreed in Error (Y)]='ZebraFish',[Disagreed in Error (Y)]) as [Disagreed in Error (Y)],
     [User ID] as [Last Qtr Conf],
     IF([Last Qtr Conf]='ZebraFish',[Last Qtr Conf]) as [User ID],
     IF(Notes='ZebraFish',Notes) as [Notes]
FROM
[CAQ.xlsx]
(ooxml, embedded labels, table is owssvr);

Directory;
LOAD [Institution ID] as [Bank ID],
     [Institution Name],
     Territory,
     [Confirmation Status],
     [Has Active CDs]
FROM
[CDARS Confirmation and Assurance Activity Report (2700).xlsx]
(ooxml, embedded labels, table is REPORT_2700)
WHERE ([Has Active CDs]='Y');

Directory;
LOAD Losing as [Bank ID],
     [Merged Reports?] as [Merged Reports],
     Winning
FROM
[CDARS_BNY Merged Banks Report (0450).xlsx]
(ooxml, embedded labels, table is REPORT_0450);

______________________________________________________

I have also bolded the spreadsheets that need to be compared in the following two statements:

• WHERE (([Bank Status]='Failed' OR [Bank Status]='Merged') AND ([Merged Reports]='N'))
• The above compares columns from the 2770 report and the 0450 report

• WHERE (([Bank Status]='Failed' OR [Bank Status]='Merged') AND ([Recip Balance]>='1000000'))
• The above compares columns from the 2770 report and the SLX report

Any thoughts on how I can do this and end up loading the desired 1589 records into my QV doc?

Thanks in advance,

Steve

[Gysbert_Wassenaar · Accepted solution]

If you want to do this in the script you have to create a table with all the fields you want to use in the where clause. A where clause is added to a load block and a load can only process one table. You'll have to use join and/or concatenate to create a table that contains all the fields you want the where clause to work on.

[#3]

That did the trick.

I used a left-join to merge everything into one table and them applied my WHERE statement.

Looks like I am good to go.

Thanks!
Steve