topic Re: combine three tables in QlikView

combine three tables

pacoli2013 — Fri, 30 Sep 2016 08:09:12 GMT

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. Syntax error, missing/misplaced FROM: HstDat_SalesInvoice:
2. Field not found - <AX_SellToCustomerNumber>

I hope someone knows how I can solve this,

Thanks in advance

regards

Court van de Lisdonk

Re: combine three tables

Anil_Babu_Samineni — Fri, 30 Sep 2016 16:46:03 GMT

Try with Join / Concatenate

What is the issue you are getting?

Re: combine three tables

nagarjuna_kotha — Fri, 30 Sep 2016 16:55:11 GMT

As per the ur above script , your problem will not solve,

How you want combine those three table without having any common fields .

please share proper information , that suite ur requiremnt.

-Nagarjun

Re: combine three tables

pacoli2013 — Fri, 30 Sep 2016 17:04:19 GMT

The problem i have is that data from sales ist 'dirty' so different data

should be excluded. For instance there are old company Numbers which should

be excluded, the temp-table has the right companynumbers. For customers:

the temp-table has Numbers from customers which don't exist and also be

excluded!!! Regards

Am Freitag, 30. September 2016 schrieb nagarjuna k :

Re: combine three tables

rupamjyotidas — Fri, 30 Sep 2016 17:17:36 GMT

Maybe this(changes in bold). But a sample app might help

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 as CustomerID

// AX_Active

FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is excluding_Customers)

WHERE (AX_Active)=1;

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey

Company,

WHERE EXISTS (including_Company);

LOAD

DataLevel,

LoadingDate,

CustomerID

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

Where not exists (CustomerID);

Re: combine three tables

rupamjyotidas — Fri, 30 Sep 2016 17:23:01 GMT

More refined:

Temp_Include:

NoConcatenate LOAD

including_Company as Company

FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is including_Companys)

WHERE (Active)=1;

Temp_Customers:

NoConcatenate LOAD

AX_SellToCustomerNumber as CustomerID

// AX_Active

FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

(ooxml, embedded labels, table is excluding_Customers)

WHERE (AX_Active)=1;

HstDat_SalesInvoice:

LOAD

Company &’’& DataLevel as CompanyKey

Company,

WHERE EXISTS (Company);

LOAD

DataLevel,

LoadingDate,

CustomerID

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

Where not exists (CustomerID);

Re: combine three tables

nagarjuna_kotha — Fri, 30 Sep 2016 17:35:32 GMT

Sorry bro, this is really not gonna help you.

Try to share small qvw file , that tell about requirement.

-Nagarjun

Re: combine three tables

rajpreeths — Fri, 30 Sep 2016 18:17:41 GMT

Hi..

You can try the following conditions to avoid those two errors..

1-》The first where exists should be replaced with following condition.

WHERE EXISTS(INCLUDING_COMPANY,COMPANY)

2-》The second where conditions can be changed as follows.

WHERE NOT EXISTS(AX_SellToCustomerNumber,Customer ID)

Please try this and let me know if this works as per your requirement

Re: combine three tables

rajpreeths — Fri, 30 Sep 2016 18:19:35 GMT

Please use field signatures as you used in your application

Re: combine three tables

rajpreeths — Fri, 30 Sep 2016 18:23:32 GMT

Use the above conditions with this load script as below..

HstDat_SalesInvoice:
LOAD

Company &’’& DataLevel as CompanyKey

Company,
DataLevel,
LoadingDate,
CustomerID

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

WHERE EXISTS(including_Company,Company)

AND not EXISTS(AX_SellToCustomerNumber,Customer ID);

Try this and let me know..

Re: combine three tables

pacoli2013 — Sat, 01 Oct 2016 08:21:51 GMT

I will, thank you in advance

Re: combine three tables

pacoli2013 — Sun, 02 Oct 2016 15:28:46 GMT

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

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

Re: combine three tables

Anil_Babu_Samineni — Mon, 03 Oct 2016 16:35:28 GMT

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?

Re: combine three tables

pacoli2013 — Mon, 03 Oct 2016 18:33:39 GMT

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

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>:

Re: combine three tables

rajpreeths — Tue, 04 Oct 2016 06:28:28 GMT

Hello,

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

Re: combine three tables

pacoli2013 — Tue, 04 Oct 2016 17:00:50 GMT

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

Re: combine three tables

pacoli2013 — Tue, 04 Oct 2016 19:54:12 GMT

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

Re: combine three tables

rajpreeths — Wed, 05 Oct 2016 06:35:59 GMT

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

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

Re: combine three tables

pacoli2013 — Wed, 05 Oct 2016 20:15:53 GMT

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