Load-function between 2 independent tables in a script?

[newqlikie]

Hello,

following problem occurs.

I need to join 2 indipendent tables (statistics-file and the article_database) in a script.

Here is my example:

The coloured part is the part I need.

Statistics_File:
load *,
if(year(Deliverydate)<='2009' and Company=Comp1,Weight*'222,00',  //PackagingCosts for Company1 till 2009 for Company1
if(year(Deliverydate)>='2010' and year(Deliverydate)<='2011' and Company=Comp1,Weight*'195,67',   //PackagingCostsfor Company1 between 2010 and 2011 for Company1
if(year(Deliverydate)>='2012' and Company=Comp1,Weight*165,87, //PackagingCostsfor Company1 since 2012
 if(year(Deliverydate)<='2009' and Company=Comp2,Weight*'222,00',  //PackagingCostsfor Company1 till 2009 for Company2
if(year(Deliverydate)>='2010' and year(Deliverydate)<='2011' and Company=Comp1,Weight*'195,67',   //PackagingCostsfor Company2 between 2010 and 2011 for Company1
if(year(Deliverydate)>='2012' and Company=Comp2,Weight*165,87, //PackagingCostsfor Company2 since 2012
)))))) as "Packagingcosts",
SQL SELECT JDJAIDRUV as DLSJAID,
    JDLIEFDAT as " Deliverydate ",
    JDGEWICHT as "Weight"
FROM QS36F.DLSJD; 
Left Join (statistics_file)
SQL SELECT JAIDRUV  as DLSJAID,
    JAARTNR as REF
FROM QS36F.DLSJA;
Article_Database:
Load *,
    if((SALIEFNR2=160210),'Comp1','Comp2') as Company;
SQL SELECT SAARTNR as REF,
    SALIEFNR2
FROM QS36F.DLSSA;

The problem is the "load" syntax in the Statistics_File, which is not working.

I know that it can not work, but so you see how it should work.

How can I solve that problem?

I appreciate your help.

Regards,

NewQlikie

[Miguel_Angel_Baeyens]

HI,

At a first glance, there are some syntax issues. Note all the following basics:

• When comparing to numeric values, don't use quotes
• When comparing to strings, values must be wraped by single quotes
• When the return is a numeric value, but the parameter has a comma as a decimal separator, you need to use the Num() function
• When loading from a SQL statement, there must be a semicolon ";" between the LOAD part and the SQL part
• When joining tables, labels are case sensitive ("Statistics_File") capitalized
• Use always LOAD to put values into memory, even if you don't want to change names or create new fields

Statistics_File:
LOAD *,
If(Year(Deliverydate) <= 2009 AND Company = 'Comp1', Num(Weight * 222),  //PackagingCosts for Company1 till 2009 for Company1
   If(Year(Deliverydate) >= 2010 AND Year(Deliverydate) <= 2011 AND Company = 'Comp1', Num(Weight * '195,67'),   //PackagingCostsfor Company1 between 2010 and 2011 for Company1
      If(Year(Deliverydate) >= 2012 AND Company = 'Comp1', Num(Weight * '165,87'), //PackagingCostsfor Company1 since 2012
         If(Year(Deliverydate) <= 2009 AND Company = 'Comp2', Num(Weight * '222,00'),  //PackagingCostsfor Company1 till 2009 for Company2
            If(Year(Deliverydate) >= 2010 AND Year(Deliverydate) <= 2011 AND Company = 'Comp1', Num(Weight * '195,67'),   //PackagingCostsfor Company2 between 2010 and 2011 for Company1
               If(Year(Deliverydate) >= 2012 AND Company = 'Comp2', Num(Weight * '165,87'), //PackagingCostsfor Company2 since 2012
)))))) AS PackagingCosts;
SQL SELECT JDJAIDRUV as DLSJAID,
    JDLIEFDAT as "Deliverydate",
    JDGEWICHT as "Weight"
FROM QS36F.DLSJD; 

LEFT JOIN (Statistics_File) LOAD *;
SQL SELECT JAIDRUV  as DLSJAID,
    JAARTNR as REF
FROM QS36F.DLSJA;

This does not mean that the above code will work just copying and pasting. I may have missed some parentheses, so double check it with your actual field names and values.

Hope that helps.

Miguel

[newqlikie]

Hi Miquel,

thanks for your help and tips.

The main problem I have is, that in the database "Statistics_File" the field "Company", from database "Article_Database", will not be found.

Article_Database:
Load *,
    if((SALIEFNR2=160210),'Comp1','Comp2') as Company;
SQL SELECT SAARTNR as REF,
    SALIEFNR2
FROM QS36F.DLSSA;

How can I solve that problem?

Thanks again for your help.

Regards

NewQlickie

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Indeed, Company is not being loaded in the SQL in the table Statistics_File. You will need to first load Article_Database then use a mapping table to pass the Company to the Statistics_File based on the common field "REF".

Article_Database:
Load *,
    If((SALIEFNR2 = 160210), 'Comp1', 'Comp2') as Company;
SQL SELECT SAARTNR as REF,
    SALIEFNR2
FROM QS36F.DLSSA;
// each REF must have ONE possible value in Company
RefCompanyMap: 
MAPPING LOAD REF,
     Company
RESIDENT Article_Database;
Statistics_File_Temp:
LOAD *;
SQL SELECT JDJAIDRUV as DLSJAID,
    JDLIEFDAT as "Deliverydate",
    JDGEWICHT as "Weight"
FROM QS36F.DLSJD; 
// The REF field is in this table, so you need to first JOIN
// Then load again as below
LEFT JOIN (Statistics_File) LOAD *; 
SQL SELECT JAIDRUV  as DLSJAID,
    JAARTNR as REF
FROM QS36F.DLSJA;
Statistics_File:
LOAD *,
     If(Year(Deliverydate) <= 2009 AND ApplyMap('RefCompanyMap', REF) = 'Comp1', Num(Weight * 222),  //PackagingCosts for Company1 till 2009 for Company1
        If(Year(Deliverydate) >= 2010 AND Year(Deliverydate) <= 2011 AND ApplyMap('RefCompanyMap', REF) = 'Comp1', Num(Weight * '195,67'),   //PackagingCostsfor Company1 between 2010 and 2011 for Company1
          If(Year(Deliverydate) >= 2012 AND ApplyMap('RefCompanyMap', REF) = 'Comp1', Num(Weight * '165,87'), //PackagingCostsfor Company1 since 2012
             If(Year(Deliverydate) <= 2009 AND ApplyMap('RefCompanyMap', REF) = 'Comp2', Num(Weight * '222,00'),  //PackagingCostsfor Company1 till 2009 for Company2
                If(Year(Deliverydate) >= 2010 AND Year(Deliverydate) <= 2011 AND ApplyMap('RefCompanyMap', REF) = 'Comp1', Num(Weight * '195,67'),   //PackagingCostsfor Company2 between 2010 and 2011 for Company1
                   If(Year(Deliverydate) >= 2012 AND ApplyMap('RefCompanyMap', REF) = 'Comp2', Num(Weight * '165,87'), //PackagingCostsfor Company2 since 2012
)))))) AS PackagingCosts
RESIDENT Statistics_File_Temp;
// The _temp table is no longer needed
DROP TABLE Statistics_File_Temp;

Hope that makes sense.

Miguel

[newqlikie]

Hi Miguel,

many thanks.

I did'nt know the "Mapping" function.

Perfect!!!

[newqlikie]

Hi Miguel,,

I still have a problem.

You wrote:

// each REF must have ONE possible value in Company
RefCompanyMap:
MAPPING LOAD REF,
     Company
RESIDENT Article_Database;

The problem ist, that many REF have the same Company-value.

If I run the scrip, I always get the message, that the field "Company" could not be found.

Thanks again for your help.

[Miguel_Angel_Baeyens]

Indeed,

You have created the mapping table, but you are not using it anyway. Check my example above on the use of the function ApplyMap() in the Statistics_File table. You need to use it in the Statistikdatei_DLSJD table.

Regards,

Miguel

[#8]

Building on Miguel's solution, I suggest you:

LOAD the Articles table as shown by Miguel

Delete the MAPPING LOAD script code segment

LOAD the Statistics temp file as shown by Miguel

LEFT JOIN the Articles table into the Statistics temp file

LOAD the final Statistics table using your original script code segment

DROP the Articles and Statistics temp tables as shown by Miguel