Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to convert SQL to Qlikview script. Can you please tell me how can I achieve this.
My SQL script is this :
SELECT
DISC_ALL_OS.HOST_ID as 'MachineID',
SW_MAN.MANUFACTURER as 'OS Manufacturer',
CAT_SW_PRODUCT.SOFTWARE as 'OS Name',
CAT_SW_EDITION.EDITION as 'OS Edition',
CASE WHEN DISC_CS_CAT_MAP.VIRTUAL=1 THEN 'Yes' WHEN DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID IS NOT NULL THEN 'No' ELSE 'N/A' END as 'Is Virtual',
DISC_ALL_OS.HARDWARE as 'Discovered - Hardware',
HW_MAN.MANUFACTURER as 'Manufacturer',
CAT_HW_PRODUCT.PRODUCT as 'Product',
CAT_HW_MODEL.MODEL as 'Model',
C1.CAT_CPU_MODEL as 'CPU Model',
CASE DISC_ALL_OS.IS_UNIQUE WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END as 'Is Unique',
FROM DISC_ALL_OS
LEFT JOIN DISC_OS_CAT_MAP ON DISC_OS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND DISC_OS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
AND DISC_OS_CAT_MAP.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN CAT_SW_PRODUCT ON DISC_OS_CAT_MAP.CAT_SW_PRODUCT_ID=CAT_SW_PRODUCT.CAT_SW_PRODUCT_ID
LEFT JOIN CAT_SW_VERSION ON DISC_OS_CAT_MAP.CAT_SW_VERSION_ID=CAT_SW_VERSION.CAT_SW_VERSION_ID
LEFT JOIN CAT_SW_VERSION_GROUP ON CAT_SW_VERSION.CAT_SW_VERSION_GROUP_ID=CAT_SW_VERSION_GROUP.CAT_SW_VERSION_GROUP_ID
LEFT JOIN CAT_SW_EDITION ON DISC_OS_CAT_MAP.CAT_SW_EDITION_ID=CAT_SW_EDITION.CAT_SW_EDITION_ID
LEFT JOIN CAT_SW_RELEASE ON DISC_OS_CAT_MAP.CAT_SW_RELEASE_ID=CAT_SW_RELEASE.CAT_SW_RELEASE_ID
LEFT JOIN CAT_MANUFACTURER SW_MAN ON DISC_OS_CAT_MAP.CAT_MANUFACTURER_ID=SW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN DISC_CS_CAT_MAP ON DISC_CS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND DISC_CS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
AND DISC_CS_CAT_MAP.COMPUTERSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN CAT_HW_PRODUCT ON DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID=CAT_HW_PRODUCT.CAT_HW_PRODUCT_ID
LEFT JOIN CAT_HW_MODEL ON DISC_CS_CAT_MAP.CAT_HW_MODEL_ID=CAT_HW_MODEL.CAT_HW_MODEL_ID
LEFT JOIN CAT_MANUFACTURER HW_MAN ON DISC_CS_CAT_MAP.CAT_MANUFACTURER_ID=HW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN MATCH_HOST_CPU C1 ON C1.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND C1.TASK_ID=DISC_ALL_OS.TASK_ID
AND C1.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
WHERE DISC_ALL_OS.INVENTORY_ID=$inventory_id$
AND DISC_ALL_OS.task_id = $task_id$
Thanks
Hi,
You can simply run this sql script using qlikview and load the data into one table..
Table:
Load *
Sql
SELECT
DISC_ALL_OS.HOST_ID as 'MachineID',
SW_MAN.MANUFACTURER as 'OS Manufacturer',
CAT_SW_PRODUCT.SOFTWARE as 'OS Name',
CAT_SW_EDITION.EDITION as 'OS Edition',
CASE WHEN DISC_CS_CAT_MAP.VIRTUAL=1 THEN 'Yes' WHEN DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID IS NOT NULL THEN 'No' ELSE 'N/A' END as 'Is Virtual',
DISC_ALL_OS.HARDWARE as 'Discovered - Hardware',
HW_MAN.MANUFACTURER as 'Manufacturer',
CAT_HW_PRODUCT.PRODUCT as 'Product',
CAT_HW_MODEL.MODEL as 'Model',
C1.CAT_CPU_MODEL as 'CPU Model',
CASE DISC_ALL_OS.IS_UNIQUE WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END as 'Is Unique',
FROM DISC_ALL_OS
LEFT JOIN DISC_OS_CAT_MAP ON DISC_OS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND DISC_OS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
AND DISC_OS_CAT_MAP.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN CAT_SW_PRODUCT ON DISC_OS_CAT_MAP.CAT_SW_PRODUCT_ID=CAT_SW_PRODUCT.CAT_SW_PRODUCT_ID
LEFT JOIN CAT_SW_VERSION ON DISC_OS_CAT_MAP.CAT_SW_VERSION_ID=CAT_SW_VERSION.CAT_SW_VERSION_ID
LEFT JOIN CAT_SW_VERSION_GROUP ON CAT_SW_VERSION.CAT_SW_VERSION_GROUP_ID=CAT_SW_VERSION_GROUP.CAT_SW_VERSION_GROUP_ID
LEFT JOIN CAT_SW_EDITION ON DISC_OS_CAT_MAP.CAT_SW_EDITION_ID=CAT_SW_EDITION.CAT_SW_EDITION_ID
LEFT JOIN CAT_SW_RELEASE ON DISC_OS_CAT_MAP.CAT_SW_RELEASE_ID=CAT_SW_RELEASE.CAT_SW_RELEASE_ID
LEFT JOIN CAT_MANUFACTURER SW_MAN ON DISC_OS_CAT_MAP.CAT_MANUFACTURER_ID=SW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN DISC_CS_CAT_MAP ON DISC_CS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND DISC_CS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
AND DISC_CS_CAT_MAP.COMPUTERSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN CAT_HW_PRODUCT ON DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID=CAT_HW_PRODUCT.CAT_HW_PRODUCT_ID
LEFT JOIN CAT_HW_MODEL ON DISC_CS_CAT_MAP.CAT_HW_MODEL_ID=CAT_HW_MODEL.CAT_HW_MODEL_ID
LEFT JOIN CAT_MANUFACTURER HW_MAN ON DISC_CS_CAT_MAP.CAT_MANUFACTURER_ID=HW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN MATCH_HOST_CPU C1 ON C1.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
AND C1.TASK_ID=DISC_ALL_OS.TASK_ID
AND C1.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
WHERE DISC_ALL_OS.INVENTORY_ID=$inventory_id$
AND DISC_ALL_OS.task_id = $task_id$
;
Hi Nikhil,
You can add following before your SQL script:
Table_Name:
Load *
Sql
SELECT
You may try something like below
LOAD
DISC_ALL_OS.HOST_ID as 'MachineID',
SW_MAN.MANUFACTURER as 'OS Manufacturer',
CAT_SW_PRODUCT.SOFTWARE as 'OS Name',
CAT_SW_EDITION.EDITION as 'OS Edition',
If(DISC_CS_CAT_MAP.VIRTUAL=1, 'Yes',If(DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID <> Null(), 'No'), 'N/A') as 'Is Virtual',
DISC_ALL_OS.HARDWARE as 'Discovered - Hardware',
HW_MAN.MANUFACTURER as 'Manufacturer',
CAT_HW_PRODUCT.PRODUCT as 'Product',
CAT_HW_MODEL.MODEL as 'Model',
C1.CAT_CPU_MODEL as 'CPU Model',
If(DISC_ALL_OS.IS_UNIQUE = 1 , 'Yes' 'No' ) as 'Is Unique'
FROM DISC_ALL_OS
LEFT JOIN (DISC_OS_CAT_MAP) where DISC_OS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and DISC_OS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
and DISC_OS_CAT_MAP.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN (CAT_SW_PRODUCT) where DISC_OS_CAT_MAP.CAT_SW_PRODUCT_ID=CAT_SW_PRODUCT.CAT_SW_PRODUCT_ID
LEFT JOIN (CAT_SW_VERSION) where DISC_OS_CAT_MAP.CAT_SW_VERSION_ID=CAT_SW_VERSION.CAT_SW_VERSION_ID
LEFT JOIN (CAT_SW_VERSION_GROUP) where CAT_SW_VERSION.CAT_SW_VERSION_GROUP_ID=CAT_SW_VERSION_GROUP.CAT_SW_VERSION_GROUP_ID
LEFT JOIN (CAT_SW_EDITION) where DISC_OS_CAT_MAP.CAT_SW_EDITION_ID=CAT_SW_EDITION.CAT_SW_EDITION_ID
LEFT JOIN (CAT_SW_RELEASE) where DISC_OS_CAT_MAP.CAT_SW_RELEASE_ID=CAT_SW_RELEASE.CAT_SW_RELEASE_ID
LEFT JOIN (CAT_MANUFACTURER SW_MAN) where DISC_OS_CAT_MAP.CAT_MANUFACTURER_ID=SW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN (DISC_CS_CAT_MAP) where DISC_CS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and DISC_CS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
and DISC_CS_CAT_MAP.COMPUTERSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
LEFT JOIN (CAT_HW_PRODUCT) where DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID=CAT_HW_PRODUCT.CAT_HW_PRODUCT_ID
LEFT JOIN (CAT_HW_MODEL) where DISC_CS_CAT_MAP.CAT_HW_MODEL_ID=CAT_HW_MODEL.CAT_HW_MODEL_ID
LEFT JOIN (CAT_MANUFACTURER HW_MAN) where DISC_CS_CAT_MAP.CAT_MANUFACTURER_ID=HW_MAN.CAT_MANUFACTURER_ID
LEFT JOIN (MATCH_HOST_CPU C1) where C1.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and C1.TASK_ID=DISC_ALL_OS.TASK_ID
and C1.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
and DISC_ALL_OS.INVENTORY_ID=$inventory_id$ and DISC_ALL_OS.task_id = $task_id$
Hi Nikhil
Try this:
load
DISC_ALL_OS.HOST_ID as [MachineID],
SW_MAN.MANUFACTURER as [OS Manufacturer],
CAT_SW_PRODUCT.SOFTWARE as [OS Name],
CAT_SW_EDITION.EDITION as [OS Edition],
If(DISC_CS_CAT_MAP.VIRTUAL=1, 'Yes',If(DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID <> Null(), 'No', 'N/A')) as [Is Virtual],
DISC_ALL_OS.HARDWARE as [Discovered - Hardware],
HW_MAN.MANUFACTURER as [Manufacturer],
CAT_HW_PRODUCT.PRODUCT as [Product],
CAT_HW_MODEL.MODEL as [Model],
C1.CAT_CPU_MODEL as [CPU Model],
If(DISC_ALL_OS.IS_UNIQUE = 1 , 'Yes' ,'No' ) as [Is Unique]
FROM DISC_ALL_OS;
LEFT JOIN
load
(DISC_OS_CAT_MAP)
where DISC_OS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and DISC_OS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
and DISC_OS_CAT_MAP.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID;
LEFT JOIN
load(CAT_SW_PRODUCT) where DISC_OS_CAT_MAP.CAT_SW_PRODUCT_ID=CAT_SW_PRODUCT.CAT_SW_PRODUCT_ID;
LEFT JOIN
load(CAT_SW_VERSION) where DISC_OS_CAT_MAP.CAT_SW_VERSION_ID=CAT_SW_VERSION.CAT_SW_VERSION_ID;
LEFT JOIN
load(CAT_SW_VERSION_GROUP) where CAT_SW_VERSION.CAT_SW_VERSION_GROUP_ID=CAT_SW_VERSION_GROUP.CAT_SW_VERSION_GROUP_ID;
LEFT JOIN
load(CAT_SW_EDITION) where DISC_OS_CAT_MAP.CAT_SW_EDITION_ID=CAT_SW_EDITION.CAT_SW_EDITION_ID;
LEFT JOIN
load(CAT_SW_RELEASE) where DISC_OS_CAT_MAP.CAT_SW_RELEASE_ID=CAT_SW_RELEASE.CAT_SW_RELEASE_ID;
LEFT JOIN
load([CAT_MANUFACTURER SW_MAN]) where DISC_OS_CAT_MAP.CAT_MANUFACTURER_ID=SW_MAN.CAT_MANUFACTURER_ID;
LEFT JOIN
load(DISC_CS_CAT_MAP) where DISC_CS_CAT_MAP.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and DISC_CS_CAT_MAP.TASK_ID=DISC_ALL_OS.TASK_ID
and DISC_CS_CAT_MAP.COMPUTERSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID;
LEFT JOIN
load(CAT_HW_PRODUCT) where DISC_CS_CAT_MAP.CAT_HW_PRODUCT_ID=CAT_HW_PRODUCT.CAT_HW_PRODUCT_ID;
LEFT JOIN
load(CAT_HW_MODEL) where DISC_CS_CAT_MAP.CAT_HW_MODEL_ID=CAT_HW_MODEL.CAT_HW_MODEL_ID;
LEFT JOIN
load([CAT_MANUFACTURER HW_MAN]) where DISC_CS_CAT_MAP.CAT_MANUFACTURER_ID=HW_MAN.CAT_MANUFACTURER_ID;
LEFT JOIN
load([MATCH_HOST_CPU C1]) where C1.INVENTORY_ID=DISC_ALL_OS.INVENTORY_ID
and C1.TASK_ID=DISC_ALL_OS.TASK_ID
and C1.OPERATINGSYSTEM_ID=DISC_ALL_OS.OPERATINGSYSTEM_ID
and DISC_ALL_OS.INVENTORY_ID=$inventory_id$ and DISC_ALL_OS.task_id = $task_id$;
Regards
Dinesh