topic Re: How can use load statement as subquery in WildMatch in QlikView

How can use load statement as subquery in WildMatch

Thu, 04 Sep 2014 04:00:29 GMT

Dear Experts,

I wonder if everyone here can help me to correct this :

[Header_VBAK]:

Inner keep Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd) WHERE not WildMatch ('VBELN', load VBELN FROM (qvd) WHERE ABSTK = 'C') And VBTYP IN ('C','I');

Thank you so much for your answers!

Re: How can use load statement as subquery in WildMatch

VishalWaghole — Thu, 04 Sep 2014 04:43:38 GMT

Hi Chantharoth,

Try this,

Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd)

WHERE not WildMatch (ABSTK,'C')

And match(VBTYP,'C','I');

Hope it will work for you.

-- Regards,

Vishal Waghole

Re: How can use load statement as subquery in WildMatch

PrashantSangle — Thu, 04 Sep 2014 04:54:40 GMT

Hi,

I dont think so this is possible in one load

You have to use not exist()

Try something like this,

test:

load VBELN

FROM (qvd)

WHERE wildmatch(ABSTK, 'C') And wildmatch(VBTYP,'C','I');

Final:

Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd)

where not exist(test.VBELN,VBELN);

Regards

Re: How can use load statement as subquery in WildMatch

MayilVahanan — Thu, 04 Sep 2014 05:05:21 GMT

Try like this

Temp:

Load VBELN FROM (qvd) WHERE ABSTK = 'C';

[Header_VBAK]:

Inner keep Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd) WHERE not Exists(VBELN,VBELN) And Match(VBTYP ,'C','I');

Re: How can use load statement as subquery in WildMatch

evan_kurowski — Thu, 04 Sep 2014 05:44:27 GMT

Hello Chantharoth,

The SQL concept of nested sub-queries is present in QlikView, but requires breaking out the nested subquery into a separate load statement that is resolved before the juncture you intend to apply it.

They're not so much nested, but rather chained together.

Re: How can use load statement as subquery in WildMatch

Thu, 04 Sep 2014 06:46:31 GMT

Dear ViVishalWaghole, max dreamer, MayilVahanan , and Evan_Kurowski,

Thank you very much for your answer. I'm so sorry.I posted wrong script. please kindly review it again :

[Header_VBAK]:

Inner keep Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd) WHERE not WildMatch ('VBELN', load VBELN FROM (qvd) WHERE ABSTK = 'C') And VBTYP IN ('C','I');

With thanks,

Chantharoth

Re: How can use load statement as subquery in WildMatch

MayilVahanan — Thu, 04 Sep 2014 06:56:48 GMT

You replace the file name from the above solution.

Temp:

Load VBELN FROM (qvd) WHERE ABSTK = 'C';

[Header_VBAK]:

Inner keep Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd) WHERE not Exists(Temp.VBELN,VBELN) And Match(VBTYP ,'C','I');

Re: How can use load statement as subquery in WildMatch

VishalWaghole — Thu, 04 Sep 2014 06:57:50 GMT

Hi Chantharoth,

VBUK:

Load VBELN

FROM (qvd)

where not wildmatch(ABSTK,'C')

and match(VBTYP,'C','I')

left join(VBUK)

Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd);

-- Regards,

Vishal Waghole.

Re: How can use load statement as subquery in WildMatch

PrashantSangle — Thu, 04 Sep 2014 06:59:32 GMT

Hi,

Try something like this,

test:

load VBELN

FROM (qvd)

WHERE wildmatch(ABSTK, 'C');

Final:

Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd)

where not exist(test.VBELN,VBELN) And wildmatch(VBTYP,'C','I');

Regards

Re: How can use load statement as subquery in WildMatch

evan_kurowski — Thu, 04 Sep 2014 07:08:26 GMT

Chantharoth Reth wrote:

Dear Vishal Waghole, max dreamer, Mayil Vahanan Ramasamy , and Evan Kurowski,

Thank you very much for your answer. I'm so sorry.I posted wrong script. please kindly review it again :

[Header_VBAK]:

Inner keep Load

VBELN AS [HeaderCode_VBELN],

applymap('AccountExecutive_VBPA',VBELN) As [PersonalNumber_PERNR],

AUDAT As [DocumentDate_AUDAT],

VKORG AS [SaleOrganization_VKORG],

KUNNR AS [CustomerCode_KUNNR],

applymap('Customer_KNA1',KUNNR) as [Customer_Name],

BUKRS_VF AS [CompanyCode_BUKRS_VF]

FROM (qvd) WHERE not WildMatch ('VBELN', load VBELN FROM (qvd) WHERE ABSTK = 'C') And VBTYP IN ('C','I');

With thanks,

Chantharoth

Hello Chantharoth,

First, there may be a missing piece. The "Inner Keep" is expecting to reduce the [Header_VBAK] table based on a key join to prior existing tables, and anyone approaching this needs to know what the key fields between [Header_VBAK] and its inner join are going to be reduced by.

For example moving the "nested subquery" out of the Wildmatch argument (where a full load statement cannot be used as an argument) and in front of the entire statement as a stand alone creates potential inner-keep reduction value on the field VBELN if it is not aliased:

[FILTER]:
load VBELN FROM (qvd) WHERE ABSTK = 'C'; //impacts INNER KEEP

[FILTER]:
load VBELN AS Aliased_VBELN FROM (qvd) WHERE ABSTK = 'C'; //avoids impacting INNER KEEP

Once the field VBELN exists prior to your Inner Join statement, the only rows that make [Header_VBAK] should be exact matches on the values in VBELN, so a "NOT Wildmatch" on VBELN will be contradictory.

I think if you confirm the fields involved in the INNER KEEP, that should remove ambiguity. As others have suggested, removing the INNER KEEP removes potential conflicts, but it's not clear whether the INNER KEEP is a requirement.