Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-11-28 01:32 AM

Convert sql to Qlikview script

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

473 Views
0 Likes
4 Replies
sushil353
Master II
Master II
‎2016-11-28 01:44 AM

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$

;

321 Views
0 Likes
Anonymous
Not applicable
‎2016-11-28 01:57 AM
Author

Hi Nikhil,

You can add following before your SQL script:

Table_Name:

Load *

Sql

SELECT

321 Views
0 Likes
Anil_Babu_Samineni
MVP
MVP
‎2016-11-28 02:08 AM

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$

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
321 Views
0 Likes
dineshm030
Creator III
Creator III
‎2016-11-28 04:52 AM

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

321 Views
0 Likes