Resident Concatenate

[dunnalahk123]

Hi,

can some one help me where i am doing wrong in below script because in my final store QVD i am not getting any rows

which i highlighted in Red in below.

ActivePCI:

Load * FROM [$(QVD_Path)\EAPActivePCIProject.qvd] (qvd)

where WildMatch([Project_Practice],'PS', 'IDS', 'TS') ;

>>> lines fetched

NoConcatenate

FACT:

LOAD [Project_Internal_ID] as [PCINEWProjectID]

FROM [$(QVD_Path)\EAPPCIDCProjects.qvd] (qvd) ;

>>> lines fetched

INNER JOIN

LOAD *, [Project_Internal_ID] as [PCINEWProjectID],

Resident ActivePCI;

>>> lines fetched

concatenate (FACT)

LOAD *, [Project_Internal_ID] as [PCILESProjectID],

Resident ActivePCI ;

>>> lines fetched

concatenate (FACT)

LOAD [Project_Internal_ID] as [PCICaaSProjectID]

FROM [$(QVD_Path)\EAPPCIGreenLakeProjects.qvd] (qvd) ;

>>> lines fetched

INNER JOIN

LOAD *, [Project_Internal_ID] as [PCICaaSProjectID],

Resident ActivePCI ;

>>> lines fetched

Drop Table ActivePCI;

NoConcatenate 

FACT1:

Load *, XXXX fields

Resident FACT;

>>> 0 lines fetched   >>>>>> Problem here why 0 lines are fetching here above when i have data in Fact when i concatenate

drop table FACT;

STORE FACT1 into [$(QVD_Path)\EAP_ActivePCI_Final_Fact.qvd];

Drop table FACT1;

EXIT SCRIPT;

[sunny_talwar]

Can you try this instead

tmpTable:
LOAD * INLINE [
Project_Practice
PS
IDS
TS
];

ActivePCI:
LOAD * FROM [$(QVD_Path)\EAPActivePCIProject.qvd] (qvd)
Where Exists([Project_Practice]);

FACT:
LOAD *,
     [Project_Internal_ID] as [PCINEWProjectID],
     'Not PCILESProjectID' as [PCILESProjectID],
     'Not PCICaaSProjectID' as [PCICaaSProjectID]
Resident ActivePCI;

Concatenate (FACT)
LOAD *,
     'Not PCINEWProjectID' as [PCINEWProjectID],
     [Project_Internal_ID] as [PCILESProjectID],
     'Not PCICaaSProjectID' as [PCICaaSProjectID]
Resident ActivePCI;

Concatenate (FACT)
LOAD *,
     'Not PCINEWProjectID' as [PCINEWProjectID],
     'Not PCILESProjectID' as [PCILESProjectID],
     [Project_Internal_ID] as [PCICaaSProjectID]
Resident ActivePCI;

tmpTable2:
LOAD [Project_Internal_ID] as [PCINEWProjectID],
     'Not PCILESProjectID' as [PCILESProjectID],
     'Not PCICaaSProjectID' as [PCICaaSProjectID]
FROM [$(QVD_Path)\EAPPCIDCProjects.qvd] (qvd);

Concatenate (tmpTable2)
LOAD 'Not PCINEWProjectID' as [PCINEWProjectID],
     [Project_Internal_ID] as [PCICaaSProjectID],
     'Not PCICaaSProjectID' as [PCICaaSProjectID]
FROM [$(QVD_Path)\EAPPCIGreenLakeProjects.qvd] (qvd);

Concatenate (tmpTable2)
LOAD 'Not PCINEWProjectID' as [PCINEWProjectID],
     'Not PCILESProjectID' as [PCICaaSProjectID],
     [Project_Internal_ID] as [PCICaaSProjectID]
FROM [$(QVD_Path)\EAPPCIGreenLakeProjects.qvd] (qvd);

Inner Join (FACT)
LOAD *
Resident tmpTable2;

STORE FACT into [$(QVD_Path)\EAP_ActivePCI_Final_Fact.qvd];

DROP Tables FACT, ActivePCI, tmpTable2, tmpTable;

EXIT SCRIPT;

[dunnalahk123]

Hi Sunny,

Thank you for your Quick Response .

Actually there a lot more joins , for your reference  i am attaching the application where you can refer to active PCI tab.

[sunny_talwar · Accepted solution]

@dunnalahk123 I see how your script is complicated, but I won't be able to reload it and even if I could, I won't know if the output I get is correct or not.

But what I was trying to demonstrate is that you need to concatenate the tables and then do a single join. Or you can create multiple tables with multiple joins and then concatenate on the joined tables. but a mix of Join concatenate join concatenate isn't going to work because the way Qlik join works based on the common field names.

[dunnalahk123]

Hi Sunny,

thank you, let me try that  based on your valuable inputs 🙂

[dunnalahk123]

Hi Sunny,

Thank You so Much . it works now as per your above inputs i changed.  first i joined and then i concatenated.

[sunny_talwar]

@dunnalahk123 awesome. I am glad you were able to figure it out