topic Nested Select in QlikView

Nested Select

Tue, 24 May 2016 07:12:58 GMT

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

Re: Nested Select

tamilarasu — Tue, 24 May 2016 08:19:32 GMT

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

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

Re: Nested Select

Kushal_Chawda — Tue, 24 May 2016 08:50:39 GMT

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

Re: Nested Select

Tue, 24 May 2016 08:59:51 GMT

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

Re: Nested Select

Tue, 24 May 2016 09:36:01 GMT

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

Re: Nested Select

tamilarasu — Tue, 24 May 2016 10:03:57 GMT

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

Re: Nested Select

Kushal_Chawda — Tue, 24 May 2016 10:07:13 GMT

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