Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
raynac
Partner
Partner

records not loading in correct order for concatenation

Hi all,

Our database stores a block of text in 60 character lines by reservation number, using suffix number as a sort so the lines are able to be presented correctly.

So the table has:

Res 1234  Suffix 10  Text

Res 1234  Suffix 20  2nd line of Text

Res 1235  Suffix 10  Text

Res 1235  Suffix 20  2nd line of Text

Res 1235  Suffix 30  3rd line of Text

Etc.

 

I want to concatenate all the lines of text into a single field.  Which seemed simple enough:

load
reservation_number as "Reservation Text Res Num",
Concat(sba_text,' ') as ResAbText

Group by reservation_number;

SQL SELECT * from fdresabtxt order by reservation_number, suffix_no asc;

But the lines concatenate in no particular order, making them nonsensical.

Then I tried:


resAbTxt:

load
reservation_number as "Reservation Text Res Num",
suffix_no as "Res Txt Suffix",
sba_text as "ResAbText1"
;

SQL SELECT * from fdresabtxt;

resAbTxt2:

load
"Reservation Text Res Num",
Concat(ResAbText1,' ') as ResAbText

resident resAbTxt
Group by "Reservation Text Res Num"
Order by "Reservation Text Res Num", "Res Txt Suffix";

drop table resAbTxt;

Still not working.

There must be a way around this.  Can anyone please tell me what it is?  What am I missing?

Thanks!

Labels (4)
1 Solution

Accepted Solutions
StarinieriG
Partner
Partner

Hi,

you could try something like this

resAbTxt:

load
reservation_number as "Reservation Text Res Num",
suffix_no as "Res Txt Suffix",
sba_text as "ResAbText1"
;

SQL SELECT * from fdresabtxt;

resAbTxt2:

load
"Reservation Text Res Num",
Concat(ResAbText1,' ',"Res Txt Suffix") as ResAbText

resident resAbTxt
Group by "Reservation Text Res Num"
Order by "Reservation Text Res Num", "Res Txt Suffix";

drop table resAbTxt;

View solution in original post

4 Replies
Frank_Hartmann
Master II
Master II

Maybe this:

Concat(YOURFIELD,' ',RowNo()) as ###Text
raynac
Partner
Partner
Author

Hi Frank, thank you so much for taking the time to assist.  I implemented your suggestion, but it did not have any effect, unfortunately!

StarinieriG
Partner
Partner

Hi,

you could try something like this

resAbTxt:

load
reservation_number as "Reservation Text Res Num",
suffix_no as "Res Txt Suffix",
sba_text as "ResAbText1"
;

SQL SELECT * from fdresabtxt;

resAbTxt2:

load
"Reservation Text Res Num",
Concat(ResAbText1,' ',"Res Txt Suffix") as ResAbText

resident resAbTxt
Group by "Reservation Text Res Num"
Order by "Reservation Text Res Num", "Res Txt Suffix";

drop table resAbTxt;

View solution in original post

raynac
Partner
Partner
Author

Brilliant!!!  Worked like a charm!  Thanks ever so much!