topic Re: SubField (product) of 2 columns in QlikView

SubField (product) of 2 columns

qluser01 — Wed, 25 Jan 2017 03:37:44 GMT

Hello!

I have a table:

ID, Col2, Col3:

1, 11|22|33, -Text1 -Text2 -Text3

2, 15|25|35, -Text4 -Text5 -Text6

3, , -Teext7 -Text8 -Text9 -Text10

Row separator for Col2 is '|'

Row separator for Col3 is '-'

I need to make separate rows from Col2 and Col3, so that each value of Col2 goes together with value (corresponds) of Col3 in each row.

So the result should be:

ID, Col2_, Col3_:

1, 11, Text1

1, 22, Text2

1, 33, Text3

2, 15, Text4

2, 25, Text5

2, 35, Text6

3, , Teext7

3, , Teext8

3, , Teext9

3, , Teext10

How can I do that?

Thanks!

Re: SubField (product) of 2 columns

sunny_talwar — Wed, 25 Jan 2017 10:15:48 GMT

May be this:

Table:

LOAD ID,

Col2,

Col3

Where Len(Trim(Col3)) > 0;

LOAD ID,

SubField(Col2, '|') as Col2,

If(Len(Trim(Col2)) = 0, SubField(Col3, '-'), SubField(Col3, '-', AutoNumber(RowNo(), ID) + 1)) as Col3;

LOAD * Inline [

ID, Col2, Col3

1, 11|22|33, -Text1 -Text2 -Text3

2, 15|25|35, -Text4 -Text5 -Text6

3, , -Teext7 -Text8 -Text9 -Text10

];

Re: SubField (product) of 2 columns

tresB — Wed, 25 Jan 2017 11:00:18 GMT

Try:

Table:

load *,

SubField(Col2,'|',IterNo()) as NewCol2,

SubField(Col3,'-',IterNo()+1) as NewCol3

While IterNo()<= RangeMax(SubStringCount(Col3,'-'),SubStringCount(Col2,'|'));

load * Inline [

ID, Col2, Col3

1, 11|22|33,-Text1-Text2-Text3

2, 15|25|35,-Text4-Text5-Text6

3, ,-Teext7-Text8-Text9-Text10

];

Re: SubField (product) of 2 columns

qluser01 — Wed, 25 Jan 2017 11:57:31 GMT

Both ways work quite well.

2 questions: where (and how) did you learn these tricks?

and the serious one:

Could you change it for another source data:

ID, Col2, Col3

1, 11|22|33, Text1| Text2| Text3

2, 15|25|35, Text4| Text5| Text6

3, , Teext7| Text8| Text9| Text10

];

where '|' is a break line char ( char(10) ) in Excel, So that we have in Excel Col2 cell

in 1 cell ( Col2 )

and corresponding:

Text1

Text2

Text3

in 1 cell

in Col3 cell to the right of the Col2 cell,

in the same row with ID = 1

etc....

Re: SubField (product) of 2 columns

qluser01 — Wed, 25 Jan 2017 12:21:43 GMT

OK I've figured out how to amend Tresesco Bs version for my needs.

The only question now is: HOW?

What to study to learn such tricks?

Thanks!

Re: SubField (product) of 2 columns

sunny_talwar — Wed, 25 Jan 2017 12:33:41 GMT

With mine, may be this?

Table:

LOAD ID,

Col2,

Col3

Where Len(Trim(Col3)) > 0;

LOAD ID,

SubField(Col2, Chr(10)) as Col2,

If(Len(Trim(Col2)) = 0, SubField(Col3, '-'), SubField(Col3, '-', AutoNumber(RowNo(), ID) + 1)) as Col3;

Not tested it, but see if that works

Re: SubField (product) of 2 columns

tresB — Wed, 25 Jan 2017 13:00:38 GMT

Change only the separator character like:

SubField(Col3,'|',IterNo()+1) as NewCol3

Re: SubField (product) of 2 columns

rahulpawarb — Wed, 25 Jan 2017 13:38:22 GMT

Could you please try below sample script:

For i=1 to 4
Data:
LOAD ID, 
     SubField(Col2,'|',$(i)) AS Col2,
     SubField(Col3,'-',$(i)) AS Col3
FROM
Source.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Next


NoConcatenate 


FinalData:
LOAD *
Resident Data
WHERE Len(Col3) > 0;


Drop Table Data;

Re: SubField (product) of 2 columns

qluser01 — Thu, 26 Jan 2017 18:53:29 GMT

So could you refer me to a study course or book or whatever to learn this and similar tricks?

Re: SubField (product) of 2 columns

sunny_talwar — Thu, 26 Jan 2017 19:09:16 GMT

Curious to know if my response worked?

and as per ways to learn these tricks, I am not sure how tresesco‌ learnt them, but I learned by helping others. The more you get involved on the community, the more diverse issues you will come across. You will have a solution, someone else have a better solution (like Tresesco did this time) and you will see why there solution was more efficient and re engineer it to understand it. In the process you get to learn more. It won't happen over-night, but as you spend more and more time you will get to learn new tricks.