topic Re: Logic in QlikView

Logic

tmumaw — Fri, 30 Sep 2016 14:20:37 GMT

Morning everyone. Does anyone see anything wrong with this logic? I am getting the message table not found Concatenate (VERIZON) Load * Resident Sprint

Thanks

Verizon:

LOAD

[Wireless Number] as WirelessNo,

[User Name],

'V' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Verizon\Cell Bills 201* - VER*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

;

Sprint:

LOAD

[Unit Name] as [User Name],

left([PTN],3) & '-' & mid([PTN],4,3) & '-' & mid([PTN], 7) as WirelessNo,

'S' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Sprint\Cell Bills 201* - SPRINT*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

;

CONCATENATE (Verizon)

LOAD *

RESIDENT Sprint;

DROP TABLE Sprint;

ATT:

LOAD

[User Name],

left([Wireless Number],3) & '-' & mid([Wireless Number],4,3) & '-' & mid([Wireless Number], 7) as WirelessNo,

'A' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\ATT\Cell Bills 201* - ATT201*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Verizon)

LOAD *

RESIDENT ATT;

DROP TABLE ATT;

RENAME Table Verizon to AllCarriers;

exit Script;

Re: Logic

sunny_talwar — Fri, 30 Sep 2016 14:25:39 GMT

May be try this:

Verizon:

LOAD

[Wireless Number] as WirelessNo,

[User Name],

'V' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Verizon\Cell Bills 201* - VER*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Verizon)

LOAD

[Unit Name] as [User Name],

left([PTN],3) & '-' & mid([PTN],4,3) & '-' & mid([PTN], 7) as WirelessNo,

'S' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Sprint\Cell Bills 201* - SPRINT*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Verizon)

LOAD

[User Name],

left([Wireless Number],3) & '-' & mid([Wireless Number],4,3) & '-' & mid([Wireless Number], 7) as WirelessNo,

'A' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\ATT\Cell Bills 201* - ATT201*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

RENAME Table Verizon to AllCarriers;

EXIT Script;

Re: Logic

tmumaw — Fri, 30 Sep 2016 14:30:18 GMT

thanks. I have used the above logic in other scripts and it works fine. Wonder what is going on with this one.

Thom

Re: Logic

sunny_talwar — Fri, 30 Sep 2016 14:33:03 GMT

See if this makes any difference:

Verizon:

LOAD

[Wireless Number] as WirelessNo,

[User Name],

'V' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Verizon\Cell Bills 201* - VER*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Sprint:

NoConcatenate

LOAD

[Unit Name] as [User Name],

left([PTN],3) & '-' & mid([PTN],4,3) & '-' & mid([PTN], 7) as WirelessNo,

'S' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Sprint\Cell Bills 201* - SPRINT*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Verizon)

LOAD *

RESIDENT Sprint;

DROP TABLE Sprint;

ATT:

NoConcatenate

LOAD

[User Name],

left([Wireless Number],3) & '-' & mid([Wireless Number],4,3) & '-' & mid([Wireless Number], 7) as WirelessNo,

'A' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\ATT\Cell Bills 201* - ATT201*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Verizon)

LOAD *

RESIDENT ATT;

DROP TABLE ATT;

RENAME Table Verizon to AllCarriers;

exit Script;

Re: Logic

marcus_sommer — Fri, 30 Sep 2016 14:34:07 GMT

If a load had the same fields like an already loaded table they will be automatically concatenated. This meant in your case that this will be enough:

AllCarriers:

LOAD

[Wireless Number] as WirelessNo,

[User Name],

'V' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Verizon\Cell Bills 201* - VER*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//Sprint:

LOAD

[Unit Name] as [User Name],

left([PTN],3) & '-' & mid([PTN],4,3) & '-' & mid([PTN], 7) as WirelessNo,

'S' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\Sprint\Cell Bills 201* - SPRINT*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//ATT:

LOAD

[User Name],

left([Wireless Number],3) & '-' & mid([Wireless Number],4,3) & '-' & mid([Wireless Number], 7) as WirelessNo,

'A' as CarrierID

FROM

[\\kent\datastore\DaveyData\IT Projects\Qlikview\cell\ATT\Cell Bills 201* - ATT201*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

- Marcus

Re: Logic

tmumaw — Fri, 30 Sep 2016 19:00:28 GMT

Thanks Marcus.