Qlik sense data modelling

[firoz24]

Hi Guys,

I have two questions 

First Question:

I have two tables with Historycard__Master and T_Final_Status both tables have common field called VIN 

I want to compare both tables VIN field values and if the VIN value that available in History card Master Table and not available in T_Final_ status tables I want to count that 

for Eg:- Table 1 :- fields VIN                                Table 2 :-  VIN

                                    HA234                                              HA233

                                    HA233                                              HA212

Here the answer is 1 after count distinct VIN because HA234 is not available in table 2 

I have attached the image below Please check 

Question 2:-

How to count matching values from a field VIN from both tables

[Rohan]

Hi @firoz24 ,

You can just create a mapping table for eg : 

Map :

mapping Load 

Vin,

'T_Final' as Flag

from  T_Final;

T:

Load *,

Applymap('Map',Vin,'N/A') as [Present in hist]

From  Historycard__Master;

& simply in Front end :

Count({<[Present in hist]={"N/A"}>} distinct Vin) // Vin present in Hist but not in T_Final

Count({<[Present in hist]={"T_Final"}>} distinct Vin) // Vin present in both.

Let me know if it works for you.

Regards,

Rohan.

[firoz24]

Hi Rohan,

Thanks for your support but it does not worked.

[firoz24]

Hi Rohan,

I have entered the script below kindly check and guide me what is the mistake if any experts can solve this kindly help.

LIB CONNECT TO 'DQMS (cq151ora_techadmin)';
[History_Card_Master]:
LOAD HCID,
HISTORYCARD,
VIN,
CCRNO,
MODEL,
"COLOR",
ENGINE,
COUNTRY,
DRIVE,
"T_M",
SYSDATE,
Date( Floor(SYSDATE),'DD-MMM-YYYY') as Final_SYSDATE,;
SQL SELECT HCID,
HISTORYCARD,
VIN,
CCRNO,
MODEL,
"COLOR",
ENGINE,
COUNTRY,
DRIVE,
"T_M",
SYSDATE
FROM DQMS.dbo."HISTORYCARD_MASTER";

//Exit Script;

LIB CONNECT TO 'DQMS (cq151ora_techadmin)';
[T_Final_Status]:
LOAD KFSID,
VIN,
Status,
Remarks,
FlagValue,
CrDate;
SQL SELECT KFSID,
VIN,
Status,
Remarks,
FlagValue,
CrDate
FROM DQMS.dbo."T_FinalStatus";

LIB CONNECT TO 'DQMS (cq151ora_techadmin)';
[VEHMODEL_Master]:
LOAD VMID as MODEL,
VEHMODEL, //,
ALIASNAME,
VLID,
VTID;
SQL SELECT VMID,
VEHMODEL,
ALIASNAME,
VLID,
VTID
FROM DQMS.dbo."VEHMODEL_MASTER";

Map :

mapping Load

Vin,

'T_Final' as Flag

from T_Final;

T:

Load *,

Applymap('Map',Vin,'N/A') as [Present in hist]

From Historycard__Master;

Exit Script;

[Rohan · Accepted solution]

Hi @firoz24 ,

Change the table names to your table names & modify your script like the following: 

LIB CONNECT TO 'DQMS (cq151ora_techadmin)';

Map :

mapping Load

VIN,

'T_Final' as Flag;

SQL SELECT Distinct
VIN

FROM DQMS.dbo."T_FinalStatus";

[History_Card_Master]:
LOAD

Applymap('Map',Vin,'N/A') as [Present in hist],

HCID,
HISTORYCARD,
VIN,
CCRNO,
MODEL,
"COLOR",
ENGINE,
COUNTRY,
DRIVE,
"T_M",
SYSDATE,
Date( Floor(SYSDATE),'DD-MMM-YYYY') as Final_SYSDATE,;
SQL SELECT HCID,
HISTORYCARD,
VIN,
CCRNO,
MODEL,
"COLOR",
ENGINE,
COUNTRY,
DRIVE,
"T_M",
SYSDATE
FROM DQMS.dbo."HISTORYCARD_MASTER";

[T_Final_Status]:
LOAD KFSID,
VIN,
Status,
Remarks,
FlagValue,
CrDate;
SQL SELECT KFSID,
VIN,
Status,
Remarks,
FlagValue,
CrDate
FROM DQMS.dbo."T_FinalStatus";

[VEHMODEL_Master]:
LOAD VMID as MODEL,
VEHMODEL, //,
ALIASNAME,
VLID,
VTID;
SQL SELECT VMID,
VEHMODEL,
ALIASNAME,
VLID,
VTID
FROM DQMS.dbo."VEHMODEL_MASTER";

Exit Script;