topic Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435 in QlikView

Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

happyjoshua777 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I tried removing an INNER JOIN in the following script:

[Carrier Decode]:

IntervalMatch (Date, [%Unique Carrier Entity Code])

LOAD

[Start Date],

If(Len([End Date]) < 1, Today(1), [End Date]) as [End Date],

[Unique Carrier Entity] as [%Unique Carrier Entity Code],

FROM

[..\3.QVD\Source\Carrier Decode.qvd]

(qvd);

Inner Join ([Carrier Decode])

LOAD

[Start Date],

If(Len([End Date]) < 1, Today(1), [End Date]) as [End Date],

[Unique Carrier Entity] as [%Unique Carrier Entity Code],

[Carrier Group ID] as [%Carrier Group ID]

FROM

[..\3.QVD\Source\Carrier Decode.qvd]

(qvd);

Left Join ([Flight Data])

Load

Date,

[%Unique Carrier Entity Code],

[%Carrier Group ID]

Resident [Carrier Decode];

Drop Table [Carrier Decode];

Drop Field Date;

Store [Flight Data] into [..\3.QVD\Transformed\'Transformed - Flight Data.qvd'];

Drop Table [Flight Data];

as follows:

[Carrier Decode]:

IntervalMatch (Date, [%Unique Carrier Entity Code])

LOAD

[Start Date],

If(Len([End Date]) < 1, Today(1), [End Date]) as [End Date],

[Unique Carrier Entity] as [%Unique Carrier Entity Code],

[Carrier Group ID] as [%Carrier Group ID] //inserted this line

FROM

[..\3.QVD\Source\Carrier Decode.qvd]

(qvd);

/*Inner Join ([Carrier Decode])

LOAD

...

[..\3.QVD\Source\Carrier Decode.qvd]

(qvd);

Left Join ([Flight Data])

Load

Date,

[%Unique Carrier Entity Code],

[%Carrier Group ID]

Resident [Carrier Decode];

Drop Table [Carrier Decode];

Drop Field Date;

Store [Flight Data] into [..\3.QVD\Transformed\'Transformed - Flight Data.qvd'];

Drop Table [Flight Data];

now I am getting the following error:

Table not found

Left Join ([Flight Data])

Load

Date,

[%Unique Carrier Entity Code],

[%Carrier Group ID]

Resident [Carrier Decode]

I do not understand how bypassing an INNER JOIN command has removed access to the Flight Date table in the Facts data tab (see below)?

thanks

======================

[Flight Data]:

LOAD

Date#(Period, 'YYYYMM') as Date ,

[%Carrier Group ID] as [OLD_Carrier Group ID],

[%Airline ID],

[%Unique Carrier Code],

[%Unique Carrier Entity Code],

[%Region Code],

[%Origin Airport ID],

[%Origin Airport Sequence ID],

[%Origin Airport Market ID],

[%Origin World Area Code],

[%Destination Airport ID],

[%Destination Airport Sequence ID],

[%Destination Airport Market ID],

[%Destination World Area Code],

[%Aircraft Group ID],

[%Aircraft Type ID],

[%Aircraft Configuration ID],

[%Distance Group ID],

[%Service Class ID],

[%Datasource ID],

[# Departures Scheduled],

[# Departures Performed],

[# Payload],

Distance,

[# Available Seats],

[# Transported Passengers],

[# Transported Freight],

[# Transported Mail],

[# Ramp-To-Ramp Time],

[# Air Time],

[Unique Carrier],

[Carrier Code],

[Carrier Name],

[Origin Airport Code],

[Origin City],

[Origin State Code],

[Origin State FIPS],

[Origin State],

[Origin Country Code],

[Origin Country],

[Destination Airport Code],

[Destination City],

[Destination State Code],

[Destination State FIPS],

[Destination State],

[Destination Country Code],

[Destination Country],

Year,

Period,

Quarter,

[Month (#)],

Month,

[From - To Airport Code],

[From - To Airport ID],

[From - To City],

[From - To State Code],

[From - To State]

FROM

[..\3.QVD\Source\Flight Data.qvd]

(qvd);

Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

Anil_Babu_Samineni — Fri, 22 Dec 2017 05:58:21 GMT

Do you have Flight Data table with you?

Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

happyjoshua777 — Fri, 22 Dec 2017 06:01:21 GMT

//Here it is thanks

[Flight Data]:

LOAD

Date#(Period, 'YYYYMM') as Date ,

[%Carrier Group ID] as [OLD_Carrier Group ID],

[%Airline ID],

[%Unique Carrier Code],

[%Unique Carrier Entity Code],

[%Region Code],

[%Origin Airport ID],

[%Origin Airport Sequence ID],

[%Origin Airport Market ID],

[%Origin World Area Code],

[%Destination Airport ID],

[%Destination Airport Sequence ID],

[%Destination Airport Market ID],

[%Destination World Area Code],

[%Aircraft Group ID],

[%Aircraft Type ID],

[%Aircraft Configuration ID],

[%Distance Group ID],

[%Service Class ID],

[%Datasource ID],

[# Departures Scheduled],

[# Departures Performed],

[# Payload],

Distance,

[# Available Seats],

[# Transported Passengers],

[# Transported Freight],

[# Transported Mail],

[# Ramp-To-Ramp Time],

[# Air Time],

[Unique Carrier],

[Carrier Code],

[Carrier Name],

[Origin Airport Code],

[Origin City],

[Origin State Code],

[Origin State FIPS],

[Origin State],

[Origin Country Code],

[Origin Country],

[Destination Airport Code],

[Destination City],

[Destination State Code],

[Destination State FIPS],

[Destination State],

[Destination Country Code],

[Destination Country],

Year,

Period,

Quarter,

[Month (#)],

Month,

[From - To Airport Code],

[From - To Airport ID],

[From - To City],

[From - To State Code],

[From - To State]

FROM

[..\3.QVD\Source\Flight Data.qvd]

(qvd);

Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

Anil_Babu_Samineni — Fri, 22 Dec 2017 06:04:09 GMT

Not sure, Which pattern is first and which is next and next-next. So, Can you help us to make it clear in QVS for full script? So, then we may dispense better

Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

happyjoshua777 — Sun, 24 Dec 2017 03:55:25 GMT

[Flight Data]:

...

is traversed first (happens to be the Fact table in this case)

then

[Carrier Decode]:

IntervalMatch (Date, [%Unique Carrier Entity Code])

LOAD

[Start Date],

...

code is traversed in the next sheet.

thanks

Re: Table not found Left Join ([Flight Data]) error after modifying a script in QV for Devs 11 p. 435

Anil_Babu_Samineni — Sun, 24 Dec 2017 06:38:07 GMT

True, When we do in script with Left Join () it should be places to the left part of the script. So, You cannot with Right join and using Left join to the Fact table.

PFA of your script and sample QVW attached with same schema