topic Re: List of data load from the SQL query in QlikView

List of data load from the SQL query

Mon, 12 Aug 2013 06:59:51 GMT

Hi All,

I need to load perticular list of records from the SAP SQL query please let me know how to do this

Ex:-

List of records:-

For example I want to load this 3 records from KONV table like

[KNUMV]

0000154697

0000154699

0000154701

[KONV]: // Conditions (Transaction Data)

Load

[MANDT] as [Client_MANDT.KONV],

[KNUMV] as [Doc. condition_KNUMV.KONV],

[KRECH] as [Calculat.type_KRECH.KONV],

[KAWRT] as [Cond.base value_KAWRT.KONV],

[KBETR] as [Amount_KBETR.KONV];

SQL Select KSCHL KOPOS KBETR KPEIN KMEIN KRECH KZBZG KUMZA KUMNE MWSK1 MWSK2 KONMS KONWS MANDT KNUMH KWERT KAWRT LIFNR KUNNR WAERS KAPPL KWAEH KNUMV KOUPD KSTBS ZAEHK STUNR KPOSN KOLNR VARCOND KDATU KSTAT KOAID STUFE WEGXX KKURS TXJLV SAKN1 SAKN2 KVSL1 KNTYP KINAK KNPRS KVSL2 KFAKTOR KRUEK KRELI KHERK KGRPE KDIFF KSTEU ZAEKO KMXAW KMXWR KDUPL KFAKTOR1 KAWRT_K KWERT_K KFKIV KVARC KMPRS PRSQU KTREL MDFLG KBFLAG from KONV;

Please it is urgent

Thanks

Re: List of data load from the SQL query

tresB — Mon, 12 Aug 2013 07:05:37 GMT

try:

[KONV]: // Conditions (Transaction Data)

Load

[MANDT] as [Client_MANDT.KONV],

[KNUMV] as [Doc. condition_KNUMV.KONV],

[KRECH] as [Calculat.type_KRECH.KONV],

[KAWRT] as [Cond.base value_KAWRT.KONV],

[KBETR] as [Amount_KBETR.KONV];

Re: List of data load from the SQL query

Kushal_Chawda — Mon, 12 Aug 2013 07:10:53 GMT

It's better to load entire table from SAP and apply filter condition at QVD level in your application.

Create qvd for KONV table.

Now while loading into the qlikview use the below script

KONV:

LOAD *

FROM KNUMV.qvd(qvd)

WHERE match([KNUMV],

'0000154697',

'0000154699',

'0000154701'

);

Re: List of data load from the SQL query

mr_janne — Mon, 12 Aug 2013 07:12:21 GMT

Hi,

something like this should also work. The "include table" can also be a text, csv or an excel table so it really doesn't matter if the needed list is long or short:

INCLUDE_TABLE:
load * inline
[KNUMV
0000154697
0000154699
0000154701
];

[KONV]: // Conditions (Transaction Data)

Load

[MANDT] as [Client_MANDT.KONV],

[KNUMV] as [Doc. condition_KNUMV.KONV],

[KRECH] as [Calculat.type_KRECH.KONV],

[KAWRT] as [Cond.base value_KAWRT.KONV],

[KBETR] as [Amount_KBETR.KONV]

where exists (KNUMV);

Re: List of data load from the SQL query

Mon, 12 Aug 2013 08:57:59 GMT

Hi,

Thanks but by this script all the previous data is also loaded link 2 crore records is loaded and then rest of the three this records

0000154697

0000154699

0000154701

is loaded but i want to read only this 3 records for incremental also because it increases the time also.

Thanks

Re: List of data load from the SQL query

Mon, 12 Aug 2013 08:59:02 GMT

Hi,

I want to load KONV incremental that is the reson to load those perticular records.

Thanks

Re: List of data load from the SQL query

Mon, 12 Aug 2013 09:22:16 GMT

Hi,

But if there is 100 or 200 KNUMV records then how to load with match also match is not working in SQL

Thnaks

Re: List of data load from the SQL query

mr_janne — Mon, 12 Aug 2013 12:51:01 GMT

If I understood correctly you would like to fetch only limited rows from SQL based on some list?

INCLUDE_TABLE:
load * inline
[KNUMV
0000154697
0000154699
0000154701
];

LET vLongString='';

FOR k = 0 to NoOfRows('INCLUDE_TABLE')-1

LET vLongString = chr(39)&Peek('KNUMV',k,'INCLUDE_TABLE')&chr(39);
IF k = 0 then

LET vRecordSet = vLongString;
else

LET vRecordSet = vRecordSet&','&vLongString;
end if
Next

drop table INCLUDE_TABLE;

Re: List of data load from the SQL query

its_anandrjs — Wed, 14 Aug 2013 09:55:00 GMT

Hi,

As i understand the requirement you need to load limited number of records from the KONV sap table i suggest use WHERE command with use of IN like

TEMP_TABLE:

load * inline

[KNUMV

0000154697

0000154699

0000154701

];

let vMax = NoOfRows('TEMP_TABLE');

for vnum = 0 to $(vMax) step 1

let vMaxVal = Peek('KNUMV',$(vnum),'TEMP_TABLE');

[KONV]: // Conditions (Transaction Data)

Load

[MANDT] as [Client_MANDT.KONV],

[KNUMV] as [Doc. condition_KNUMV.KONV],

[KPOSN] as [Item_KPOSN.KONV],

[STUNR] as [Step number_STUNR.KONV],

Load statements

[KBFLAG] as [BitFlags_KBFLAG.KONV];

Where KNUMV IN ('$(vMaxVal)');

Try This code please

Let me know about this

Thanks & Best Regards,

Anand