topic Re: Resident Concatenate in QlikView

Resident Concatenate

dunnalahk123 — Fri, 15 Nov 2024 23:55:13 GMT

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;

Re: Resident Concatenate

sunny_talwar — Fri, 25 Sep 2020 13:19:46 GMT

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;

Re: Resident Concatenate

dunnalahk123 — Fri, 25 Sep 2020 14:21:29 GMT

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.

Re: Resident Concatenate

sunny_talwar — Fri, 25 Sep 2020 14:28:15 GMT

@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.

Re: Resident Concatenate

dunnalahk123 — Fri, 25 Sep 2020 14:47:47 GMT

Hi Sunny,

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

Re: Resident Concatenate

dunnalahk123 — Tue, 29 Sep 2020 04:37:38 GMT

Hi Sunny,

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

Re: Resident Concatenate

sunny_talwar — Tue, 29 Sep 2020 11:58:57 GMT

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