Exists() function doesn't work in QS with applymap pls check

[PrernaI]

I am working on migrating QV to the QS project, which has this script. When I run same script with QlikView it works bt in Qs i get 0 value 

pls check and give the response at earlist 

mapping:

Mapping

Load * Inline [

Period_PERIO, Month

001, 4

002, 5

003, 6

004, 7

005, 8

006, 9

007, 10

008, 11

009, 12

010, 1

011, 2

012, 3

];

COEP:

LOAD [GL Code] as [Cost Element],

TYPE,

[Group Under],

[GL Name]

FROM

[lib://Qliksense Qvds (kelkargroup_qlikadm)/GL LIST FOR OPEX BUDG 19-20.xlsx]

//[..\Qvds\GL LIST FOR OPEX BUDG 19-20.xlsx]

(ooxml, embedded labels, table is [GL LIST]);

left join(COEP)

LOAD ApplyMap('mapping',Period_PERIO) AS MONTH1,

[Company Code_BUKRS]&'-'& Right([Object number_OBJNR],10) as %KEY_COEP_CSKS,

[CO Area_KOKRS] as [Controlling Area], 

     [Object number_OBJNR],

//      [Cost Element],

      [Cost Element_KSTAR],

     Right([Object number_OBJNR],10) as [Cost Center Code],

     [Fiscal Year_GJAHR] as [Fiscal Year], 

[Company Code_BUKRS] as [Company Code],

     [Cost Element_KSTAR] as [Cost Element], 

     [Value TranCurr_WTGBTR] as [Value TranCurr], 

     [Value/Obj. Crcy_WOGBTR] as [Value/Obj. Crcy], 

     [Val/COArea Crcy_WKGBTR] as [Val/COArea Crcy], 

     [Trans. Currency_TWAER] as [Trans. Currency], 

     [Object Currency_OWAER] as [Object Currency], 

     [Posting row_BUZEI],

     Period_PERIO ,   

     [Document Number_BELNR] as [Contrilling Document Number],

     Name_SGTXT as Name,

      Right(AuxAcctAsmnt_1_OBJNR_N1,8) as [Employee Code],

     [Purchasing Doc._EBELN] as Purchase_NO,

     if(wildmatch(Period_PERIO,'010','011','012'),MakeDate([Fiscal Year_GJAHR]+1, ApplyMap('mapping',Period_PERIO),1),MakeDate([Fiscal Year_GJAHR], ApplyMap('mapping',Period_PERIO),1)) as Period,

     month(if(wildmatch(Period_PERIO,'010','011','012'),MakeDate([Fiscal Year_GJAHR]+1, ApplyMap('mapping',Period_PERIO),1),MakeDate([Fiscal Year_GJAHR], ApplyMap('mapping',Period_PERIO),1))) as MONTH

FROM

[lib://Qliksense Qvds (kelkargroup_qlikadm)/COEP.QVD]

(qvd) Where WildMatch([Object number_OBJNR],'KS*') and [Fiscal Year_GJAHR]>= Year(Today())-2 

and Exists([Cost Element],[Cost Element_KSTAR])

;

[marcus_sommer]

Make sure that the entire script is really equally - each slight change might have an impact. Further take a look on the environment - means the interpretation variables, region settings, time-zones, user-rights, network-settings, ... which may change which data were really loaded and how they are interpreted.

A quite quick check would be to load the join-load without the join as an own table without the conditions as filter else including them as extra columns. Further helpful is often to include recno() and rowno() to track the records in regard to the sources and/or any comparing and making each record unique.

In this way you would see all loaded data and how they are interpreted and if the condition-columns return the expected results. I assume you will find some differences between View and Sense and this should hint for the underlying reasons.

[marksouzacosta]

Hi @PrernaI,

Remove the left join(COEP) and the and Exists([Cost Element],[Cost Element_KSTAR]). Reload your app and check how both tables are linking (which field(s)).

After reloaded, open the Data Model Viewer and select the key field in in each one of the tables and check the tags and the shape/format of the values. You MUST have something in the Tags on both fields. If you don't have anything in the tag field or if the values of both field tags don't match, you have a problem - send us a screen shot.

Also, check key field subset ratio on both tables and let us know.

Regards,

Mark Costa

[Andrea_Bertazzo]

I would suggest to check the following:

1. There may be an Exists() Function Timing Issue

The Exists([Cost Element],[Cost Element_KSTAR]) in your WHERE clause checks if values exist in a field that hasn't been loaded yet. In your script:

You load COEP table first (which creates [Cost Element] field)
Then you do a left join(COEP) that tries to use Exists([Cost Element],...)

Problem: During the left join load, the [Cost Element] field may not be fully populated yet, causing Exists() to return false for all rows.

I would suggest to reorder the script:

// Load the mapping first
mapping:
Mapping Load * Inline [
Period_PERIO, Month
001, 4
002, 5
...
];

// Load COEP first to establish the Cost Element field
COEP:
LOAD [GL Code] as [Cost Element],
TYPE,
[Group Under],
[GL Name]
FROM [lib://Qliksense Qvds (kelkargroup_qlikadm)/GL LIST FOR OPEX BUDG 19-20.xlsx]
(ooxml, embedded labels, table is [GL LIST]);

// Create a temporary table with the Exists check
TEMP_COEP:
LOAD
ApplyMap('mapping',Period_PERIO) AS MONTH1,
[Company Code_BUKRS]&'-'& Right([Object number_OBJNR],10) as %KEY_COEP_CSKS,
// ... rest of fields
FROM [lib://Qliksense Qvds (kelkargroup_qlikadm)/COEP.QVD] (qvd)
WHERE WildMatch([Object number_OBJNR],'KS*')
and [Fiscal Year_GJAHR]>= Year(Today())-2
and Exists([Cost Element],[Cost Element_KSTAR]);

// Then join
LEFT JOIN(COEP) LOAD * RESIDENT TEMP_COEP;
DROP TABLE TEMP_COEP;

2. Check the Field Name Case Sensitivity

Period_PERIO values in your QVD match exactly (with leading zeros: '001', '002', etc.)
[Cost Element_KSTAR] field name is exactly as it appears in the QVD

3. Data Connection Path

Ensure your library connection lib://Qliksense Qvds (kelkargroup_qlikadm)/ is properly configured and accessible.