topic Re: To show one record against each project code in App Development

To show one record against each project code

Aspiring_Developer — Tue, 15 Sep 2020 09:13:57 GMT

Hello Everyone,

i am facing a small issue.

I have two tables below in qlik:-

---------------------------------------------------------------

oporte:
LOAD
Photo,

Date(Date#(Photo, 'YYYYMM'), 'MM/DD/YYYY') as Date,

Left(Photo,4) as Year,

upper( "Customer or Brand") as Customer,
Project as [Project Code],
Project as P1,

Project & left(Photo,4) as %Key_Year,//to link dates
"Average Hourly Rate" as [Avg Hourly Rate],
-("Development costs" / "Development hours" * 1000) as [Average Hourly Rate]

From[.qvd]
(qvd);

--------------------------------------------------------------------

Qualify *
Unqualify %Key_Year;

PAMS:

LOAD
Project as [Project Code],
Company,
Project as P2,
Company as Company_Code, //LCC Rate %Customer as PAMS_Cust,
[Calendar Year/Month],
[Calendar Year],
Project & [Calendar Year] as %Key_Year,
Actual,
date( date#( "Calendar Year/Month"&'01','YYYYMMDD')) as Currentmonthstart,

if(len(Actual)> 0, Actual, 0) as new_Actual,//Replacing null value with 0
if(len(RB)> 0, RB, 0) as New_RB,

RB

FROM
[\\SDCTWt0228\Sources\POAE\QVDs\PAMS\PAMS.qvd]
(qvd)

where Date(Date#([Calendar Year], 'YYYY'),'DD/MM/YYYY') >= Date(YearStart((today()),-3))

and Company <> '#' and Customer <>'#';

Unqualify *;

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

I link the two tables on the basis of project code and Year to sync the dates for the selection .

Now when i select a project code it is having two different customers. How can i show both the customers against one single project code. Do i need to join them or concatenate them (but both tables have different structure)

Please pleas ehelp as i am not able to understand what to do in this

Re: To show one record against each project code

Jonathan_Dienst — Tue, 15 Sep 2020 10:09:20 GMT

I only see a single customer value in you sample

Re: To show one record against each project code

Aspiring_Developer — Tue, 15 Sep 2020 10:17:25 GMT

There are two customers against Single Project code

Customer -- Coming from table 1--MERCEDES

PAMS.Customer -- Coming from table 2--DAIMILER

Re: To show one record against each project code

Aspiring_Developer — Tue, 15 Sep 2020 10:30:08 GMT

the desired output i am looking for is :-

Project Code Customer Value

C09136 MERCEDES 10

C09136 DAMILER 20

Attached the sample qvw for the same

@Kushal_Chawda @sunny_talwar

Re: To show one record against each project code

Kushal_Chawda — Tue, 15 Sep 2020 11:55:45 GMT

@Aspiring_Developer Your column name should be same in both the tables which you are concatenating.

try to change the column for oporte table as below. You can change the column of any table but Idea is to have field name common in both the table.

oporte:
LOAD
//Photo,

Date(Date#(Photo, 'YYYYMM'), 'MM/DD/YYYY') as PAMS.Date1,

Left(Photo,4) as [PAMS.Calendar Year],

upper( "Customer or Brand") as Customer,

Project as [PAMS.Project Code],

//Project as P1,

Poject & left(Photo,4) as %Key_Year,//to link dates

// Project & Photo as %Key_Date,

"Average Hourly Rate" as [Avg Hourly Rate],
-("Development costs" / "Development hours" * 1000) as [Average Hourly Rate]

From[\\SDCTWt0228\Sources\POAE\QVDs\OPORTe\OPORTe*.qvd]
(qvd);

rename the [Average Hourly Rate] from table based on matching field name in PAMS table

Re: To show one record against each project code

Aspiring_Developer — Tue, 15 Sep 2020 12:56:00 GMT

@Kushal_Chawda We do not have same set of data in both the tables. For example [Average Hourly Rate] is there in table1 but not in table 2