topic Re: Transposing or eliminating repeating rows in QlikView

Transposing or eliminating repeating rows

Fri, 08 Aug 2014 17:24:28 GMT

[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

Re: Transposing or eliminating repeating rows

Fri, 08 Aug 2014 17:55:25 GMT

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');

Re: Transposing or eliminating repeating rows

JonnyPoole — Fri, 08 Aug 2014 17:59:29 GMT

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;

Re: Transposing or eliminating repeating rows

MarcoWedel — Fri, 08 Aug 2014 18:11:57 GMT

Please post some test data

thanks

regards

Marco

Re: Transposing or eliminating repeating rows

MarcoWedel — Fri, 08 Aug 2014 22:47:03 GMT

[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

Re: Transposing or eliminating repeating rows

Sat, 09 Aug 2014 19:37:22 GMT

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

Re: Re: Transposing or eliminating repeating rows

MarcoWedel — Sat, 09 Aug 2014 20:56:30 GMT

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