How can use load statement as subquery in WildMatch

[#1]

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!

[VishalWaghole]

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

[PrashantSangle]

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

,

[MayilVahanan]

Hi

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

[evan_kurowski]

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.

[#6]

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

[MayilVahanan · Accepted solution]

Hi

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

[VishalWaghole]

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.

[PrashantSangle]

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

[evan_kurowski]

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.