Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
FD:
QUALIFY *;
UNQUALIFY [J YEAR],BRANCH_CLEAN,[J MONTH],[Month Name],CATEGORY,ITEM, JEWELLERY_TYPE;
LOAD STATE,
BR_CODE,
J_YEAR AS [J YEAR],
BRANCH,
BRANCH_CLEAN,
VOUCHER_NO,
J_MONTH AS [J MONTH],
MONTH_NAME AS [Month Name],
VOUCHER_DATE,
DATE(VOUCHER_DATE,'DD-MMM-YYYY') as [VOU_DATE],
CUSTOMER_NAME,
CATEGORY,
ITEM_CODE,
LABEL_DATE,
KARIGAR_CODE,
KARIGAR_NAME,
SALE_TYPE,
LABEL_NO,
ITEM,
ITEM_DESCRIPTION,
NANG,
PCS,
GROSS_WT,
NET_WT,
CARAT,
GOLD_VALUE_SP,
MAKING_SP,
DIA_VALUE_SP,
BILLED_MAKING_CHARGES,
ACTUAL_MAKING_CHARGES,
BILLED_OTHER_CHARGES,
OTHER_DISCOUNT,
BILL_AMOUNT as qq,
CALCULATION_BASIS_MAKING,
CALCULATION_BASIS_GOLD,
APPROVAL_ID,
APPROVAL_NAME,
STYLE,
STYLENAME,
JEWELLERY_TYPE,
JEWELLERY_TYPE_NAME,
PURITY,
VAT,
EXCISE,
TCS,
ADVANCE_NUMBER,
VARITY,
BASE_GOLD_RATE,
PCS_CARAT,
COLOR_CLARITY,
// COLOR_CLARITY_1,
CREATION_DATE,
ADVANCE_BASE_GOLD_RATE,
GOLD_RATE_CP,
MAKING_CHARGES_CP,
OTHER_CHARGES_CP,
DIAMOND_VALUE_CP,
TOTAL_ITEM_COST,
STONE_CAT,
//STONE_CAT_1,
PCS_OF_STONE,
// PCS_CARAT_1,
STONE_CODE,
STONE_RATE,
DIA_RANGE,
STONE_QUALITY,
STONE_CLARITY,
DESIGN_NUMBER,
JEWELLERY_COLLECTION_FLAG,
EMP_CODE,
EMP_NAME,
IGST,
SGST,
CGST,
GST_NO,
DISCOUNT,
NET_BILLED_AMT as D.BILL_AMOUNT,
NET_BILLED_AMT,
VOUCHER_NO&'*****'&[LABEL_NO] AS VOUCHER_NO_LABEL_NO
FROM
[D:\DATA\QVD\XXPOS_SALES_FINAL_D_P_O.QVD]
(qvd);
Concatenate
LOAD STATE,
BR_CODE,
J_YEAR AS [J YEAR],
BRANCH,
BRANCH_CLEAN,
VOUCHER_NO,
J_MONTH AS [J MONTH],
MONTH_NAME AS [Month Name],
VOUCHER_DATE,
DATE(VOUCHER_DATE,'DD-MMM-YYYY') as [VOU_DATE],
CUSTOMER_NAME,
CATEGORY,
ITEM_CODE,
LABEL_DATE,
KARIGAR_CODE,
KARIGAR_NAME,
SALE_TYPE,
LABEL_NO,
ITEM,
ITEM_DESCRIPTION,
NANG,
PCS,
GROSS_WT,
NET_WT,
CARAT,
GOLD_VALUE_SP,
MAKING_SP,
DIA_VALUE_SP,
BILLED_MAKING_CHARGES,
ACTUAL_MAKING_CHARGES,
BILLED_OTHER_CHARGES,
OTHER_DISCOUNT,
BILL_AMOUNT as qq,
CALCULATION_BASIS_MAKING,
CALCULATION_BASIS_GOLD,
APPROVAL_ID,
APPROVAL_NAME,
STYLE,
STYLENAME,
JEWELLERY_TYPE,
JEWELLERY_TYPE_NAME,
PURITY,
VAT,
EXCISE,
TCS,
ADVANCE_NUMBER,
VARITY,
BASE_GOLD_RATE,
PCS_CARAT,
COLOR_CLARITY,
//COLOR_CLARITY_1,
CREATION_DATE,
ADVANCE_BASE_GOLD_RATE,
GOLD_RATE_CP,
MAKING_CHARGES_CP,
OTHER_CHARGES_CP,
DIAMOND_VALUE_CP,
TOTAL_ITEM_COST,
STONE_CAT,
// STONE_CAT_1,
PCS_OF_STONE,
// PCS_CARAT_1,
STONE_CODE,
STONE_RATE,
DIA_RANGE,
STONE_QUALITY,
STONE_CLARITY,
DESIGN_NUMBER,
JEWELLERY_COLLECTION_FLAG,
EMP_CODE,
EMP_NAME,
IGST,
SGST,
CGST,
GST_NO,
DISCOUNT,
NET_BILLED_AMT as GBillAmount ,
NET_BILLED_AMTFROM
[D:\DATA\QVD\XXPOS_SALES_GOLD.QVD]
(qvd);
for the second table i want to use qualify and unqualify
I don't think You Can do this. In such cases I always do the concatenation/union first and then use Qualify/unqualify to have aliases.
If you want to know the data source You can add filename() to the final table.
Had to look this one up in Help:
What you are doing is fine, I believe the problem you ran into is you tried to use the field alias instead of the original field name, and the alias is not available until post load, so you need to use the original fieldnames in the Unqualify instead of the alias names, I believe that should get things to work as you wish at that point. Hopefully I have gotten this correct.
Regards,
Brett
Hi there, here is the solution - I had the exact same issue, and I found this to work perfectly. Good luck:
Set vYourDatabaseConnect = '[Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourCatalog;Data Source=YourSeverName;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=YourWorkStation;Use Encryption for Data=False;Tag with column collation when possible=False]';
OLEDB CONNECT TO $(vYourDatabaseConnect);
UnQUALIFY *;
QV_Table1: //This is the first QlikView Table
SQL
Select
Feild1 As FirstField
,Feild2 As SecondField
,Feild3 As ThirdField
From DB_Table1;
OLEDB CONNECT TO $(vYourDatabaseConnect); //Can be a different server
UnQUALIFY *;
Concatenate(QV_Table1)
QV_Table2: //This is the second QlikView Table
SQL
Select
Feild1 As FirstField
,Feild2 As SecondField
,Feild3 As ThirdField
From DB_Table2;
QUALIFY*;
UNQUALIFY FirstField;
QV_Table: //Resident load that will allow me to qualify all the fields and then unqualify specific fields
LOAD *
Resident QV_Table1;
Drop Table QV_Table1;
//Data will be Concatenated in the QlikView Table "QV_Table"