Transposing or eliminating repeating rows

[#1]

[Loss Mit Steps]:

LOAD
[Loan Number] as lm_ln_no,
[Ls Actual Completion Date],
[[Ls Step Code],
[Record Change Code] ,

if (match([Ls Step Code],'483'), [Ls Actual Completion Date]) as s483_Dt ,
if (match([Ls Step Code],'367'), [Ls Actual Completion Date]) as s367_Dt

FROM
(qvd)
where match([Record Change Code],'A','C')
and match([Ls Step Code],'483','367','324')
and([Ls Actual Completion Date])>0;

The output looks something like this

lm_ln_no              Ls_Actual_Compl_Dt               s483_Dt              s367_Dt         Record Change Cod

000111                 07/01/2014                             07/01/2014                                             A

000112                05/15/2014                             05/15/2014                                                A

000112               06/15/2014                                                           06/15/2014                    A

The issue here is if a loan has both step cd scenarios the row repeats.  Notice 00012.  I need for the loan to appear once and the date show side by side

lm_ln_no              Ls_Actual_Compl_Dt               s483_Dt              s367_Dt         Record Change Cod

000111                 07/01/2014                             07/01/2014                                   A

000112                05/15/2014                             05/15/2014        06/15/2014             A

[#2]

Is '483' the [Ls Step Code] that you want to use for the rest of the fields? If so try something like this:

[Loss Mit Steps]:

LOAD

[Loan Number] as lm_ln_no,

[Ls Actual Completion Date],

[Ls Actual Completion Date] AS s483_Dt,

[Ls Step Code],

[Record Change Code]

FROM

(qvd)

where match([Record Change Code],'A','C')

and match([Ls Step Code],'483')

and([Ls Actual Completion Date])>0;

LEFT JOIN

LOAD

[Loan Number] as lm_ln_no,

[Ls Actual Completion Date] as s367_Dt

FROM

(qvd)

where match([Ls Step Code],'367');

[JonnyPoole]

i'm kind of guessing without access to the source data, but i suggest you load the 483s in first, then load the 367s and join it back to the first table on lm_ln_no. 

I'm not sure what the 324s are for or the step code and record change code so the actual script is a guess:

Table:

LOAD
[Loan Number] as lm_ln_no,
[Ls Actual Completion Date],
[[Ls Step Code],
[Record Change Code] ,

if (match([Ls Step Code],'483'), [Ls Actual Completion Date]) as s483_Dt ,

FROM
(qvd)
where match([Record Change Code],'A','C')
and match([Ls Step Code],'483','324')
and([Ls Actual Completion Date])>0;

join (Table)

LOAD
[Loan Number] as lm_ln_no,

if (match([Ls Step Code],'367'), [Ls Actual Completion Date]) as s367_Dt

FROM
(qvd)
where match([Record Change Code],'A','C')
and match([Ls Step Code],'367','324')
and([Ls Actual Completion Date])>0;

[MarcoWedel]

Please post some test data

thanks

regards

Marco

[MarcoWedel]

[Loss Mit Steps]:
LOAD * INLINE [
    Loan Number, Ls Actual Completion Date, Ls Step Code, Record Change Code
    000111, 07/01/2014, 483, A
    000112, 05/15/2014, 483, A
    000112, 06/15/2014, 367, A
];

tabStepCodeDates:
Generic LOAD
  [Loan Number],
  's'&[Ls Step Code]&'_Dt',
  [Ls Actual Completion Date]
Resident [Loss Mit Steps];

hope this helps

regards

Marco

[#6]

The output I listed here was just for example purposes.  In reality I do not want to have to hard code that data as it is just an example.  I have 2 tables.  One called table_main and the other is Loss_Mit_Steps.  Since one loan can have both a 483_Dt and a 367_Dt or say another date, if that loan does have multiple dates it wil appear in multiple rows.  What I am trying to do is format the output to do a left join from from table_main and Loss_Mit_Steps and just show the loan number once.  I need to show the 483_Dt and the 367_Dt on the same row.  The INLINE program forces me to hardcode values.  That is not what I want to do

[MarcoWedel]

Hi,

I'm sorry for not explaining my solution well enough:

The inline load was meant to create some test data because I didn't have your T_LOSS.qvd.

What I tried to show in fact was the subsequent generic load which creates seperate 'sxxx'_Dt fields for ANY [LS step code] that might occur, without the need for hard coded step code values or field names.

So you would just insert this part after loading your Loss_Mit_Steps table:

Generic LOAD
  [Loan Number],
  's'&[Ls Step Code]&'_Dt',
  [Ls Actual Completion Date]
Resident [Loss Mit Steps];

hope this explains well enough

regards

Marco