topic Re: Adding/Replicate column in QlikView

Adding/Replicate column

Anonymous — Tue, 14 Apr 2015 11:38:53 GMT

Hi Community

I have below two table (F1 is Fact and R1 is Reference)

I only need to column SID to the table F1 for given CDate and FW. Like

For 1st row in F1 will be like For 2nd row in F1 will be like For 3rd row in F1 will be like

30-Sep-14, CCAR, 101, 1 30-Sep-14, DFAST, 104, 6 31-Dec-14, DFAST, 125, 9

30-Sep-14, CCAR, 101, 2 30-Sep-14, DFAST, 104, 7 31-Dec-14, DFAST, 125, 10

30-Sep-14, CCAR, 101, 3 30-Sep-14, DFAST, 104, 8 31-Dec-14, DFAST, 125, 11

30-Sep-14, CCAR, 101, 4

30-Sep-14, CCAR, 101, 5

And all in one table.

I tried left join but unable to apply two resident tables with filter condition.

F1:

LOAD * INLINE [
CDate, FW, AVal
30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104
31-Dec-14, DFAST, 125
];

R1:
LOAD * INLINE
[
CDate, FW, Syno, SID
30-Sep-14, CCAR, F B, 1
30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3
30-Sep-14, CCAR, B B, 4
30-Sep-14, CCAR, B A, 5
30-Sep-14, DFAST, F B, 6
30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8
31-Dec-14, DFAST, B B, 9
31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
];

Two inline tables definition attached for reference

Thanking you

Re: Adding/Replicate column

anbu1984 — Tue, 14 Apr 2015 11:51:51 GMT

Check this app

F1:

LOAD * INLINE [

CDate, FW, AVal

30-Sep-14, CCAR, 101

30-Sep-14, DFAST, 104

31-Dec-14, DFAST, 125

];

Left Join(F1)

R1:

LOAD * INLINE

[

CDate, FW, Syno, SID

30-Sep-14, CCAR, F B, 1

30-Sep-14, CCAR, F A, 2

30-Sep-14, CCAR, F SA, 3

30-Sep-14, CCAR, B B, 4

30-Sep-14, CCAR, B A, 5

30-Sep-14, DFAST, F B, 6

30-Sep-14, DFAST, F A, 7

30-Sep-14, DFAST, F SA, 8

31-Dec-14, DFAST, B B, 9

31-Dec-14, DFAST, B A, 10

31-Dec-14, DFAST, B SA, 11

];

Re: Adding/Replicate column

Gabriel — Tue, 14 Apr 2015 11:58:42 GMT

Hi,

Try this

LOAD * INLINE [

CDate, FW, AVal

30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104

31-Dec-14, DFAST, 125

];

LEFT JOIN(F1)

LOAD * INLINE

[

CDate, FW, Syno, SID

30-Sep-14, CCAR, F B, 1

30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3

30-Sep-14, CCAR, B B, 4

30-Sep-14, CCAR, B A, 5

30-Sep-14, DFAST, F B, 6

30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8

31-Dec-14, DFAST, B B, 9

31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
]
WHERE Exists (CDate) ;

Re: Adding/Replicate column

manojkulkarni — Tue, 14 Apr 2015 11:58:43 GMT

F1:

LOAD * INLINE [

]

Left Join(F1)

R1:

LOAD * INLINE [

]

should work

Re: Adding/Replicate column

Anonymous — Tue, 14 Apr 2015 12:05:10 GMT

Hi Gabriel

Your anser is helpful but the question is WHERE Exist(CDate) is reqired?

Without this I also achieve result.

Thanking you

Re: Adding/Replicate column

Anonymous — Tue, 14 Apr 2015 12:06:31 GMT

Hi Anbu

Your suggestion works greatly, but in addition to this if there are two resident tables. Can we add filter on those?

Thanking you

Re: Adding/Replicate column

anbu1984 — Tue, 14 Apr 2015 12:09:55 GMT

What is your filter?

Re: Adding/Replicate column

manojkulkarni — Tue, 14 Apr 2015 12:10:46 GMT

If you have two tables to be joined , then you can add flag to differentiate...

Re: Adding/Replicate column

nagarjuna_kotha — Tue, 14 Apr 2015 12:19:45 GMT

Hi Satish,

Use "LEFT JOIN" you will achieve your requirement.

Regards,

Nagarjuna

Re: Adding/Replicate column

Anonymous — Tue, 14 Apr 2015 12:20:14 GMT

Manoj

I appreciate.

Your suggestion make sense so I added

Thanks