topic Load-function between 2 independent tables in a script? in QlikView

Load-function between 2 independent tables in a script?

newqlikie — Thu, 12 Jan 2012 11:35:12 GMT

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

Re: Load-function between 2 tables in a script possible?

Miguel_Angel_Baeyens — Thu, 12 Jan 2012 12:30:42 GMT

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

Re: Load-function between 2 tables in a script possible?

newqlikie — Thu, 12 Jan 2012 13:20:06 GMT

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

Re: Load-function between 2 tables in a script possible?

Miguel_Angel_Baeyens — Thu, 12 Jan 2012 13:53:04 GMT

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

Load-function between 2 independent tables in a script?

newqlikie — Thu, 12 Jan 2012 15:03:20 GMT

Hi Miguel,

many thanks.

I did'nt know the "Mapping" function.

Perfect!!!

Re: Load-function between 2 independent tables in a script?

newqlikie — Thu, 12 Jan 2012 16:52:53 GMT

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.

Re: Load-function between 2 tables in a script possible?

Miguel_Angel_Baeyens — Thu, 12 Jan 2012 17:42:26 GMT

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

Load-function between 2 independent tables in a script?

Fri, 13 Jan 2012 05:03:51 GMT

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