combine three tables

[pacoli2013]

Hello,

I have a Problem perhaps for you a very little one to solve. Let me explain: there are three tables:

Temp_Include:
NoConcatenate  LOAD
including_Company
FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is including_Companys)
WHERE (Active)=1;

Temp_Customers:
NoConcatenate LOAD
AX_SellToCustomerNumber
//     AX_Active
FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Customers)
WHERE (AX_Active)=1;

and

HstDat_SalesInvoice:
LOAD

Company &’’& DataLevel as CompanyKey

Company,
DataLevel,
LoadingDate,
CustomerID

FROM [$(DATADIR)qvd\Sales.qvd] (qvd);

I want to make one table out of three. There should be one table HstDat_SalesInvoice, where  company is equal including_company from table Temp_Include and CustomerID not equal AX_SellToCustomerNumber from Temp_Customers:

I think it should be something like this:

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey
Company,
WHERE EXISTS (including_Company);
LOAD       
DataLevel,
LoadingDate,

CustomerID

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)
Where (CustomerID <> AX_SellToCustomerNumber);

I got the  next messages when I reload:

1. 1. Syntax error, missing/misplaced FROM: HstDat_SalesInvoice:
2. 2. Field not found - <AX_SellToCustomerNumber>

I hope someone knows how I can solve this,

Thanks in advance

regards

Court van de Lisdonk

[pacoli2013]

I will, thank you in advance​

[pacoli2013]

Hello,

Your Solution worked, thank you for that. Perhaps you can help me one more time. If I want to make an IF-statement during the load like this

i

f ((Company = $(including_Company))and (CustomerID <> $(AX_SellToCustomerNumber)), Year(Postingdate)) as Year

I got a message:

error in expression: )' expected

HstDat_SalesInvoice:

LOAD

Company &' '& DataLevel      as DimHierPrimDimCompanyKey,

Company,

     DataLevel,

     LoadingDate,

PostingDate,

     CustomerID,

    if ((Company = )and (CustomerID <> ), Year(Postingdate)) as Year

FROM

(qvd)

I thought I made it correct. Can it be that I cannot use $(including_Company) and $(AX_SellToCustomerNumber)?

This is my LOAD-command/page is :

Temp_Include:
NoConcatenate LOAD
  including_Company
FROM
(ooxml, embedded labels, table is including_Companys)
WHERE (Active)=1;

Temp_Customers:
// Left Join(LimitedTable)
NoConcatenate LOAD
   AX_SellToCustomerNumber
FROM
(ooxml, embedded labels, table is excluding_Customers)
WHERE (AX_Active)=1;

HstDat_SalesInvoice:

LOAD

Company &' '& DataLevel      as DimHierPrimDimCompanyKey,

Company,

     DataLevel,

     LoadingDate,

PostingDate,

     CustomerID,

    if ((Company = $(including_Company))and (CustomerID <> $(AX_SellToCustomerNumber)), Year(Postingdate)) as Year

FROM

(qvd);

Thanks in advance

[Anil_Babu_Samineni]

Instead of that you can use this if those Two are fields

if (Company = including_Company and CustomerID <> AX_SellToCustomerNumber, Year(Postingdate)) as Year

Does it make sense, or else let me know what is your intend?

[pacoli2013]

Hello,

I have changed the loading statement, but now i got the message Field not

found -< including_Company>

When I make a where-statement at the end (after

(qvd)) I don't get a message and the field is found. Do I miss

something????? Is the place of the IF-statement wrong, or should I map the

table with the including_Company field?

The scripteditor give no signs that something is wrong

HstDat_SalesInvoice:

LOAD

Company &' '& DataLevel

as

DimHierPrimDimCompanyKey,

Company,

DataLevel,

LoadingDate,

PostingDate,

CustomerID,

if ((Company = including_Company ) and (CustomerID<>

AX_SellToCustomerNumber), Year(PostingDate)) As LimitedPostingDate

FROM

(qvd)

;

Field not found - AX_ItemNumber ),

LineAmount, NULL()) As LimitedLineAmount

FROM

(qvd)

​With regards​

Court van de Lisdonk

Email: courtvdlisdonk@gmail.com

2016-10-03 18:35 GMT+02:00 Anil babu <qcwebmaster@qlikview.com>:

[rajpreeths]

Hello,

Could you please tell me the logic for making the POSTINGYEAR so that I can help you further..????

[pacoli2013]

Hello,

The reason I want Year from PostingDate as  LimitedPostingDate is that I needed further in the script to make a kind of select load. Perhaps the name LimitedPostingDate is wrong and i should have named the field LimitedYear, this year is only wanted when Company = including_Company and CustomerID <> X_SellToCustomerNumber), normally   LimitedPostingDate or LimitedYear is this Year: My script has variable vYear which is 2016, in the maintab I have a SET vYear = year(date(now()))

Including_Company are companynumbers which are old and not longer used AX_SellToCustomerNumbers are numbers from another, old system and also no longer in use.

Hope you see the Logic now and can help me solving my problem

hanks in advance

[pacoli2013]

Hello. I have an other question. I looking for a good Book which can help

me by my questions in scripting, a Kind of bible for scripting. Perhaps you

know such Kind of Book

Thanks and regards

Court vd Lisdonk

[rajpreeths]

Please find the answers in inline

Hello,

I have changed the loading statement, but now i got the message Field not

found -< including_Company>

When I make a where-statement at the end (after

(qvd)) I don't get a message and the field is found. Do I miss

something?????

Yes.you will get a message such if a field called 'including_Company'  is not avaliable in your  HstDat_SalesInvoice.qvd

Is the place of the IF-statement wrong, or should I map the

table with the including_Company field?

You can left join the two tables if needed -- > It depends on your data model design approach

The scripteditor give no signs that something is wrong

HstDat_SalesInvoice:

LOAD

            Company &' '& DataLevel

                                                  as

DimHierPrimDimCompanyKey,

            Company,

            DataLevel,

    LoadingDate,

            PostingDate,

    CustomerID,

        if  ((Company = including_Company ) and (CustomerID<>

AX_SellToCustomerNumber), Year(PostingDate)) As LimitedPostingDate

  FROM

(qvd)

;

Field not found - AX_ItemNumber ),

LineAmount, NULL()) As LimitedLineAmount

FROM

(qvd)

The above script can be modified as follows if you do not want  to join HstDat_SalesInvoice with a table which has including_Company  company field.(Temp_Include table)

HstDat_SalesInvoice:
LOAD

Company &’’& DataLevel as CompanyKey

Company,
DataLevel,
LoadingDate,
CustomerID,

Year(PostingDate)) As LimitedPostingDate

FROM [$(DATADIR)qvd\Sales.qvd] (qvd)

WHERE EXISTS(including_Company,Company)

AND not EXISTS(AX_SellToCustomerNumber,Customer ID);

Please share your application here for further queries.

Rajpreeth S

[pacoli2013]

Thank you for your answer. I think it will be a left join. Because my

Manager don't want the exist-solution. He thinks it won't work. So I will

make some joins with temp-tables and drop them after the joins