Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I hope someone knows how I can solve this,
Thanks in advance
regards
Court van de Lisdonk
I will, thank you in advance
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
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?
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>:
Hello,
Could you please tell me the logic for making the POSTINGYEAR so that I can help you further..????
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
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
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
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