Need generic scrip for multiple customers

[#1]

Hi All,

I need to load the data from a file by using generic scrip for multiple customers. But, the customers are using different libraries for the same files.

For example:

CUST1 using the file ARPMAST from library PP01DAT01

CUST2 using the file ARPMAST from library PPSEDAT01 and so on.

I use to write the SQL scrip as below:

SQL SELECT * FROM PP01DAT01.ARPMAST

But, this is for a specific customer. L

Could you please tell me how I can load the data by using generic scrip for multiple customers?

Regards,

Venugopal.

[johnw]

There would probably be an approach that looks something like this:

Customers:
LOAD * INLINE [
Customer, Database
CUST1, PP01DAT01.ARPMAST
CUST2, PPSEDAT01.ARPMAST
etc.
];

FOR I = 1 TO the number of rows in Customers
set a variable to the correct database
SQL SELECT * FROM that variable;
NEXT

But that seems more complicated than just writing the SQL for each customer:

SQL SELECT * FROM PP01DAT01.ARPMAST;
SQL SELECT * FROM PPSEDAT01.ARPMAST;

The only advantage I can think of is if you can load the list of customers and databases from some existing file or database. Is that the situation?

[#3]

Hi John,

Thanks a lot for your help. Is it possible to load all the ARPMAST file data from different libraries (PP01DAT01, PPSEDAT01 etc) into a single table?

If So, how I can differentiate a particular record from which library?

I would like to write a script as below:

Load A,B,C,D;

From PP01DAT01.ARPMAST;

From PPSEDAT01.ARPMAST;

Etc.

Here A, B, C and D are fields in ARPMAST file.

Regards,

Venugopal.

[johnw]

Like this:

MyTable:
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PP01DAT01.ARPMAST;
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PPSEDAT01.ARPMAST;
etc.

As long as the list of fields is the same, QlikView will concatenate the new data onto the existing table. I tend to always do the concatenation explicitly, though. I think it makes it more clear what's going on for maintenance purposes, even if it's only strictly necessary when the list of fields is different, but you still want to concatenate the new data.

MyTable:
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PP01DAT01.ARPMAST;
CONCATENATE (MyTable)
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PPSEDAT01.ARPMAST;
etc.

[#5]

Hi John,

Thanks a lot for your help.

Is it possible to find a particular record from which library?

For example, I have merged 5 records from file ARPMAST in library PP01DAT01 and 5 more records from file ARPMAST in library PPSEDAT01.

MyTable:
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PP01DAT01.ARPMAST; /*(5 records)*/
LOAD A,B,C,D; SQL SELECT A,B,C,D FROM PPSEDAT01.ARPMAST; /*(5 records)*/

How I can find a particular record from which library (i.e either PP01DAT01 or PPSEDAT01)?

Thanks a lot for your kind help. Your help is very much useful to my application.

Regards,

Venugopal.

[jonathandienst · Accepted solution]

Hi

Just add a field to indicate the source:

MyTable:
LOAD A,B,C,D, 'PP01DAT01' AS Source ; SQL SELECT A,B,C,D FROM PP01DAT01.ARPMAST;
LOAD A,B,C,D, 'PPSEDAT01' AS Source ; SQL SELECT A,B,C,D FROM PPSEDAT01.ARPMAST;
etc.

Jonathan