topic Reg:SQL Script Help in QlikView

Reg:SQL Script Help

Tue, 28 Feb 2012 16:18:58 GMT

Hi All,

I have a following query.My data will be look like this

Table 1

Key	Supply1	Supply2	Supply3	Supply4
Pav12	Source1	Source2
Pav13	Source1	Source2	Source3

]

Table 2

Key

SourceNo

Decvice id

Pav12

Dev1

Pav12

Dev3

Pav12

Dev3

Pav13

Qwe1

Pav13

Qwe12

Pav13

Qwe14

And based on above tables my oputput data should be look like this

Key	SourceNo
Pav12	1
Pav12	2
Pav13	1
Pav13	2
Pav13	3

.For Pav12 key the first table having value only on Source 1 and Source 2 so from table second I need to take sourceNo of 1 and 2 only,even though its having source no 3

So based on Supply fields from table first I need to show the corresponding source Number. Since the qvw directly connected with Oracle database (No qvd

suggest me in either SQL Script or Load statment to achive it.

-Jai

Reg:SQL Script Help

rbecher — Tue, 28 Feb 2012 18:32:44 GMT

Hi Jai,

this should be easy:

SELECT T1.Key, T2.SourceNo

FROM Table1 T1, Table2 T2

WHERE T1.Key = T2.Key

AND (

T1.Supply1 = 'Source' || T2.SourceNo

OR T1.Supply2 = 'Source' || T2.SourceNo

OR T1.Supply3 = 'Source' || T2.SourceNo

OR T1.Supply4 = 'Source' || T2.SourceNo

);

- Ralf

Reg:SQL Script Help

Wed, 29 Feb 2012 09:57:31 GMT

Hi Ralf,

Thank you for your reply.Actually I am not able to compare fields T1.Supply fields against T2.Source no,since both the fields where diffrent in data type.So can you check and let me know once again?i

-Jai

Reg:SQL Script Help

rbecher — Wed, 29 Feb 2012 11:59:44 GMT

The comparision is between T1.SupplyX and a string. The double pipe means a concatenation in Oracle including a cast of T2.SourceNo to string (or varchar exactly).

- Ralf

Reg:SQL Script Help

Thu, 01 Mar 2012 05:55:27 GMT

Hi Ralf,

Really thanks a lot for your clarification.But I am facing two issues with your solution,that is

1.Due to comparision between two diffrent data type fields, it is taking more timeand it is not allowing other operaitons in the DB level

2.It woul be better if I get generic solution rather than hard coding.

Kindly help me out,if possible...

-Jai

Re: Reg:SQL Script Help

deepakk — Thu, 01 Mar 2012 06:32:33 GMT

hi,

I hope the attach application helps you out.

Deepak

Reg:SQL Script Help

Thu, 01 Mar 2012 07:18:54 GMT

Hi Deepak,

Thank you for your reply,Actually still I am getting all the source Nos,Since Key field contains the all the values in second table.Kindly let me if there is any other way...

-Jai

Reg:SQL Script Help

deepakk — Thu, 01 Mar 2012 07:33:55 GMT

hi Jai,

Did you used the left join ? . If it is still appearing the data model is different , its not as same as you have suggested

above.

If you can add a sample application then we can check.

Deepak

Reg:SQL Script Help

rbecher — Thu, 01 Mar 2012 07:39:24 GMT

If you do a join in QV it has to use the same conditions as in SQL.

- Ralf

Re: Reg:SQL Script Help

rbecher — Thu, 01 Mar 2012 08:42:46 GMT

There is kinda generic solution with crosstable and join in QV:

Table1:

LOAD * INLINE [

Key, Supply1, Supply2, Supply3, Supply4

Pav12, Source1, Source2, ,

Pav13, Source1, Source2, Source3,

];

T1CrossTable:

CROSSTABLE (Supply, Source, 1) Load * Resident Table1;

drop table Table1;

Result:

NOCONCATENATE LOAD Key, num#(replace(Source, 'Source','')) as SourceNo

Resident T1CrossTable

Where len(trim(Source))>0;

drop table T1CrossTable;

//Table2:

INNER JOIN LOAD * INLINE [

Key, SourceNo, DeviceID

Pav12, 1, Dev1

Pav12, 2, Dev2

Pav12, 3, Dev3

Pav13, 1, Qwe1

Pav13, 2, Qwe12

Pav13, 3, Qwe14

];

- Ralf

Re: Reg:SQL Script Help

rbecher — Fri, 02 Mar 2012 13:50:25 GMT

Hi Jai,

have you tried this?

- Ralf

Reg:SQL Script Help

Fri, 09 Mar 2012 06:51:58 GMT

Hi Ralf,

Applogies for late reply.And thanks a lot for your reply.It was done in diffrent way. We have achieved by creating key field with four source fields. And we have linked the same with base table.

-Jai