Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me to get all the below rows in straight table into one row like below expected output
Expected output from image
ID Hire Date Training1 Com Date Training2 Com Date Training3 Com Date Training4 Com Date
68064 22/03/1999 20/02/2018 13/07/2018 08/03/2018 -
Here all the above fields are dimensions in the backend. The script is
Load
ID,
Hire Date,
if(Training = 'Training 1', [Completion Date] ) as "Training1 Com Date",
if(Training = 'Training 2', [Completion Date] ) as "Training2 Com Date",
if(Training = 'Training 3', [Completion Date] ) as "Training3 Com Date",
if(Training = 'Training 4', [Completion Date] ) as "Training4 Com Date"
From [lib://Source/Training.qvd] (qvd);
Please help me to get one row per one id in the backend.
Thanks in advance
Hi,
you will have your required output if you will join tables one by one.
Table:
Load
ID,
Hire Date,
if(Training = 'Training 1', [Completion Date] ) as "Training1 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 2', [Completion Date] ) as "Training2 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 3', [Completion Date] ) as "Training3 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 4', [Completion Date] ) as "Training4 Com Date"
From [lib://Source/Training.qvd] (qvd);
Hope that helps
If you uploaded a sample of your raw data, it will be easier for us to help.
Hi,
you will have your required output if you will join tables one by one.
Table:
Load
ID,
Hire Date,
if(Training = 'Training 1', [Completion Date] ) as "Training1 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 2', [Completion Date] ) as "Training2 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 3', [Completion Date] ) as "Training3 Com Date"
From [lib://Source/Training.qvd] (qvd);
Left join (Table)
Load
ID,
if(Training = 'Training 4', [Completion Date] ) as "Training4 Com Date"
From [lib://Source/Training.qvd] (qvd);
Hope that helps