Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Nested Select

Hi,

Please help me with the following.

In need to select values from SAP table based on field values from another SAP table, i.e. select from select

Script part underneath in bold is the part I am needing assistance with?

//////////////////////////////////////////////////////////

[VBAK]: //Sales Document Header Data

LOAD VBELN AS [Sales Document],

ERNAM AS [Created by],

AUDAT AS [Document Date],

KNUMV AS [Doc. condition no.],

where

AUDAT > '20160501'

LEFT JOIN ([VBAK])

LOAD KNUMV AS [Doc. condition no.],

KSCHL AS [Condition type],

KBETR AS [Condition rate]

;

SQL SELECT

KNUMV,

KSCHL,

KBETR

FROM KONV

WHERE

KNUMV =

(Load [Doc. condition no.],

Resident VBAK);

//////////////////////////////////////////////////////////

Field KNUMV is an indexed field, if I only use a left join the query runs for hours instead of seconds.

Thanks in advance

6 Replies
tamilarasu
Not applicable

Re: Nested Select

Hi Mome,

Try this,

//////////////////////////////////////////////////////////

[VBAK]: //Sales Document Header Data

LOAD VBELN AS [Sales Document],

ERNAM AS [Created by],

AUDAT AS [Document Date],

KNUMV AS [Doc. condition no.],

where

AUDAT > '20160501'

LEFT JOIN ([VBAK])

LOAD KNUMV AS [Doc. condition no.],

KSCHL AS [Condition type],

KBETR AS [Condition rate];

Temp:

Load Concat(Distinct Chr(39) & [Doc. condition no.] & Chr(39),', ') as List resident VBAK;

LET vList = Peek('List', 0, 'Temp');

Drop table Temp; 


SQL SELECT

KNUMV,

KSCHL,

KBETR

FROM KONV

WHERE

Match(KNUMV, $(vList));

//////////////////////////////////////////////////////////

kush141087
Not applicable

Re: Nested Select

try this

[VBAK]: //Sales Document Header Data

LOAD VBELN AS [Sales Document],

ERNAM AS [Created by],

AUDAT AS [Document Date],

KNUMV AS [Doc. condition no.],

where

AUDAT > '20160501';

Temp:

Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

resident VBAK;


LET vList = Peek('List', 0, 'Temp');

Drop table Temp;


LEFT JOIN ([VBAK])

LOAD KNUMV AS [Doc. condition no.],

KSCHL AS [Condition type],

KBETR AS [Condition rate];

SQL SELECT

KNUMV,

KSCHL,

KBETR

FROM KONV

WHERE

Match(KNUMV, $(vList));


Not applicable

Re: Nested Select

Hi Tamil & Kushal, thank you for the feedback,

I see you have similar solutions,

I have tried both in the exact format you send them, but I get the same script error:

i.e.

////////////////////////////////////////////

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "(" is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)

SQL SELECT

KNUMV,

KSCHL,

KBETR

FROM KONV

WHERE

Match (KNUMV, '0117978213', '0117978214', '0117979191')

////////////////////////////////////////////


Am I making an obvious syntax error or may it be that SAP Connector doesn't recognize this command?


Regards


Not applicable

Re: Nested Select

I have replaced the statement Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

with

Load Distinct Concat('KNUMV = ' & Chr(39) & [Doc. condition no.] & Chr(39),' or ') as List

Works perfectly!

Thanks again

tamilarasu
Not applicable

Re: Nested Select

Morne,

Ah!! I forgot to see the SQL statement. Match is a Qlikview function and it won't work in Sql script. Glad that you found the solution!


You can try like below


Where KNUMV IN ($(vList));

kush141087
Not applicable

Re: Nested Select

try this instead

[VBAK]: //Sales Document Header Data

LOAD VBELN AS [Sales Document],

ERNAM AS [Created by],

AUDAT AS [Document Date],

KNUMV AS [Doc. condition no.],

where

AUDAT > '20160501';

Temp:

Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

resident VBAK;


LET vList = Peek('List', 0, 'Temp');

Drop table Temp;


LEFT JOIN ([VBAK])

LOAD KNUMV AS [Doc. condition no.],

KSCHL AS [Condition type],

KBETR AS [Condition rate];

SQL SELECT

KNUMV,

KSCHL,

KBETR

FROM KONV

WHERE KNUMV in ($(vList))